Table question?

[Bob]

I have a table(1) of data ("A1:B35"). Col "A" has names and Col "B" has values.
How do I set up table(2) which only copies data from table(1) that has
values?

 

[Don Guillett]

More detail. Do you mean you want to copy col a:col b IF col b has no info.

 

[Bob]

Copy col a and col b only if there is a value in col b.
ie

Table1
lindup 23
law 0
low 7

What I'm after in table is as follows:

Table2
lindup 23
low 7

 

[Max]

One simple formulas setup which will achieve it for you ..

With source data in A1:B1 down as per your post
In D1: =IF(B1="","",ROW())
In E1: =IF(ROW()>COUNT($D:$D),"",INDEX(A:A,SMALL($D:$D,ROW())))
Copy E1 to F1. Select D1:F1, copy down to cover the max expected extent of
source data, say down to F100? Minimize/hide col D. Cols E & F will return
the required dynamic results neatly packed at the top, ie only the names &
"values" from cols A & B where col B isn't blank.

And if your source data starts in row2 down (instead of row1)
use this set of point formulae instead
In D2: =IF(B2="","",ROW())
Leave D1 empty
In E2:
=IF(ROWS($1:1)>COUNT($D:$D),"",INDEX(A:A,SMALL($D:$D,ROWS($1:1))))

You got it up and going over there?
High-five it here, click the YES button below

P/s: Btw, think you should have replied/clarified further to Shane in your
earlier thread instead of starting a new thread here, with ensuing
discussions continuing from thereof. That's the naturally efficient &
courteous way to engage the many helpful responders.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik

 

[Max]

See my response for one way to get there
(It crossed your reply here)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik

 

[Don Guillett]

something like:
sub copyif()
counter=2
for i= 2 to cells(rows.count,"b").end(xlup).row
if cells(i,"b")>0 then rows(i).copy sheets("destshet").cells(counter,"a")
'or
'if cells(i,"b")>0 then cells(i,"a").resize(,2).copy
sheets("destshet").cells(counter,"a")
counter=counter+1
next i
end sub


Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Bob]

Thanks Max, I'm having trouble with this site, as a reasult this question has
been posted twice, thinking it did not load the first time.
Your formula is not working properly. I am still getting data (from col "A"
that has 0 or empty cell in col"B") pasted on the new table. (Does that make
sense!!!?)
It needs to look down col "B" and copy rows(col A & B) that have values in
them.

 

[Max]

Clueing in from your line

.. still getting data (from col "A" that has 0 or empty cell in col"B") pasted

you could try changing the earlier criteria
to this:
In D1: =IF(OR(TRIM(B1)="",B1=0),"",ROW())
Remember to copy D1 down after you change it

It should work fine for you
Post back, celebrate your success by clicking the YES button below ..
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---

 

[Bob]

Many thanks Max. It's working brilliantly.
I am still having problems with the site. For some reason it keeps getting
interupted but I shall battle on.
Once again thanks very much.

 

[Max]

You're welcome, Bob. Thanks for feeding back, and the rating.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik