retrieve value from column based on separate column

dhx10000

Board Regular
Joined
Jul 11, 2005
Messages
144
I have a worksheet that contains data similar to this:

A B
--------- -------------
578 22395
578 22395
625 37553
703 37553
811 49995
811 49995
823 49995

-----------------------------------------------------
I have another sheet where I am trying to do this:

G H
----- --------
578 22395
625 37553
703 37553
811 49995
823 49995

I already know how to generate the "G" column. I am having problems generating the "H" column

Thank you
 

dhx10000

Board Regular
Joined
Jul 11, 2005
Messages
144
I have a correction to make, what if columns A and B are reversed? Because that is how my data is. I don't think VLOOKUP will work.
 

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,647
That's a good point... I never tried reverse search... Now you made me wonder too...
 

dhx10000

Board Regular
Joined
Jul 11, 2005
Messages
144
This is excellent. The last piece of my puzzle is that sometimes my VLOOKUP is returning a 0 in my H column because the H column contains some blanks. Is there any way to tell the VLOOKUP funtion to only return the first non-blank value it comes across, per value in G?

A B
-- --
578
578
578 22995
625
625 36150
735 37555
855 39562


This would in turn look like this:

G H
-- --
578 22995
625 36150
735 37555
855 39562
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
The safest thing to do might be (if it's random, or you might not find a match) would be to make a copy of your data, then delete the blanks in column B, then do your lookup, e.g., delete code:

Code:
Sub foo()
Range("b:b").SpecialCells(xlBlanks).EntireRow.Delete
End Sub
 

Forum statistics

Threads
1,078,005
Messages
5,337,689
Members
399,160
Latest member
Vantone

Some videos you may like

This Week's Hot Topics

Top