Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
taxmom
 
Posts: n/a
Default IF & VLOOKUP FORMULA

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.


  #2   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

I would use the formula as is, then select and copy everything as values
somewhere else maybe a
new sheet, then I would apply datafilteradvanced 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

--

Regards,

Peo Sjoblom


"taxmom" wrote in message
...
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.




  #3   Report Post  
Roger Govier
 
Posts: n/a
Default

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


--
Regards
Roger Govier
"taxmom" wrote in message
...
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.




  #4   Report Post  
taxmom
 
Posts: n/a
Default

THANK YOU SO MUCH!


"Roger Govier" wrote:

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


--
Regards
Roger Govier
"taxmom" wrote in message
...
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.





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Match / Vlookup within an Array formula Hari Prasadh Excel Discussion (Misc queries) 3 February 3rd 05 05:37 PM
What wrong with VLOOKUP formula TARZAN Excel Worksheet Functions 2 January 31st 05 11:09 PM
how to use vlookup formula kirky Excel Worksheet Functions 2 January 25th 05 06:23 PM
How do I use Range Names listed in a VLookup table in a formula? Essbasedvlpr32 Excel Worksheet Functions 3 December 15th 04 11:11 PM
formula using both vlookup & hlookup xFreeAdvice Excel Worksheet Functions 2 November 18th 04 12:19 AM


All times are GMT +1. The time now is 10:20 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"