IF & VLOOKUP FORMULA

[Guest]

I got the formula suggested yesterday to work, IF(b2="ABC
Printing",VLOOKUP("ABC Printing",b2:i8,2,FALSE),""). When the formula is
entered on each line going down it will leaves blank line when the data is
not found. I changed the formula to the one below, however it brings up
another issue. The formula put on each line can create duplicate data. How
do I modify the formula below to avoid dublicate data? or How do I modify the
formula above to avoid blank lines? Is it possible?

The senario: vendor worksheet
vendor ck# amount date
A1 ABC Printing 504 100 1/5/2005
A2 SBC 505 100 1/5/2005
A3 Super Coups 506 300 1/5/2005
A4 ABC Printing 507 100 2/7/2005
A5 SBC 508 300 2/8/2005
A6 ABC Printing 509 200 2/28/2005

The receiptient worksheet
Date Ck # Amount
Formula under Check # 1st line
IF(a2="ABC Printing",b2,VLOOKUP("ABC Printing",a2:d7,2,FALSE)) This would
return the 1st line data
Formula under Ck # 2nd line
IF(a3="ABC Printing",b3,VLOOKUP("ABC Printing",a3:d7,2,FALSE)) This would
return line 4 data
Formula under Ck # 3rd line
IF(a4="ABC Printing",b4,VLOOKUP("ABC Printing",a4:d7,2,FALSE)) This would
return line 4 data again

thanks for any suggestions.

 

[Peo Sjoblom]

I would use the formula as is, then select and copy everything as values
somewhere else maybe a
new sheet, then I would apply data>filter>advanced filter, select unique
records only and copy to another location,
now there should be only one blank line that you can remove manually by
deleting that particular row and
there should be no duplicates either

 

[Roger Govier]

Hi

I would use Advanced Filter to achieve this rather than your lookup
formulae.
Take a look at Debra Dalgleish's site to see some examples on extracting to
another sheet
http://www.contextures.com/xladvfilter01.html

 

[Guest]

THANK YOU SO MUCH!

Hi

I would use Advanced Filter to achieve this rather than your lookup
formulae.
Take a look at Debra Dalgleish's site to see some examples on extracting to
another sheet
http://www.contextures.com/xladvfilter01.html