Sorting, HELP!

  • Thread starter Thread starter DA
  • Start date Start date
D

DA

I have a worksheet (database) with 11 columns, say,
columns A to K. I have more than 500 rows (sort everyday),
say, starts with row 1.

Col A = Number
Col B = Name of State
Col C to Col K = Other info.

I have to sort the above database into 2 tables.

Table 1 = If A1 is more than 20, I have to copy the info
on all the columns so, my formula across the columns for
each row is:
=IF(A1>=20,+A1,"")

Table 2 = Exclude if A1 is ZERO (0) and the State is
Hawaii (HI) so, my formula across the colums for each row
is:
=IF(AND($A1=0,$B1="HI"),"",+A1)

Problem:
If the formula does not meet the criteria, the row is
blank.

Is there a better or easier way to do it to:
a) don't leave any blank rows
b) fill the rows automatically

Thanks
 
Hi
try the following formula on your second sheet for getting all rows
with column A>20:
in A1 enter the array formula (with CTRL+SHIFT+ENTER)
=INDEX('sheet1'!$A$1:$A$1000,SMALL(IF('sheet1'!$A$1:$A$1000>20,ROW('she
et1'!$A$1:$A$1000)),ROW()))

in B1 enter
=VLOOKUP(A1,'sheet1'!$A$1:$C$1000,2,0)

C1 enter:
=VLOOKUP(A1,'sheet1'!$A$1:$C$1000,3,0)
copy all three formulas down

For the second table change the formula in A1 to the array formula
=INDEX('sheet1'!$A$1:$A$1000,SMALL(IF(('sheet1'!$A$1:$A$1000=0)*('sheet
1'!$B$1:$B$1000="HI"),ROW('sheet1'!$A$1:$A$1000)),ROW()))

B1 and C1 are the same as above
 
Hi,
Thanks for your time and effort.
The formula in table 1 is ok.
The formula in table 2 does not work. It should include
everything EXCEPT if the number is 0 and the state is HI.

Also, is there away to make the cell/row blank where the
formulas are? Right now, it shows, #NUM! if there is no
data to sort or LOOKUP.

Thanks again
 
Hi
so for the second one change the formula to
=INDEX('sheet1'!$A$1:$A$1000,SMALL(IF(('sheet1'!$A$1:$A$1000<>0)+('shee
t
1'!$B$1:$B$1000<>"HI"),ROW('sheet1'!$A$1:$A$1000)),ROW()))

To prevent the error messages change the formula in A1 to
=IF(ISERROR(INDEX(....)),"",INDEX(...))

and the formulas in B1/C1 change to
=IF(A1<>"",VLOOKUP(...),"")
 
I got the formula on table 2! I changed ")*(" to "),("
based on the formula on table 1 that you provided.

How about the #NUM!? Can you help me on it too?

I appreciate your help!!!!!!!!!!!!!!
 
Hi,
It still doesn't work, even mine! I was so excited I
thought I got it right. Can you please check the formula
again for table 2? The first formula you gave me was the
opposite. It copied the data if the number is 0 and the
state it HI. I need a formula that would get all the data
EXCEPT if the number is 0 and the state is HI.

How will I "insert" the error formula? Before index?
Sorry, I'm still new at these things.
 
Hi
what is exactly wrong with my second formula. That is what wrong result
did you get?

For the question if the rror formula. Just replace the part
INDEX(...)
and VLOOKUP(...)

with the original formulas I gave you. e.g.
for A1:
=IF(ISERROR(INDEX('sheet1'!$A$1:$A$1000,SMALL(IF(('sheet1'!$A$1:$A$1000
<>0)+('shee
t1'!$B$1:$B$1000<>"HI"),ROW('sheet1'!$A$1:$A$1000)),ROW()))),"",INDEX(.
...))

B1:
=IF(A1<>"",VLOOKUP(A1,'sheet1'!$A$1:$C$1000,2,0),"")
 
Hi,
I think my information is incomplete that's why you can't
come up with the right formula. In column B, there are
several names of States, i.e., CA, TX, NY, etc. I need all
the information even if the number is 0 except for HI.

Thanks again.
 
Hi
try
=INDEX('sheet1'!$A$1:$A$1000,SMALL(IF('sheet1'!$B$1:$B$1000<>"HI",ROW('
sheet1'!$A$1:$A$1000)),ROW()))
 
Back
Top