Need to compare two columns and fetch the matching values in a new column

[aghaffar82]

Kindly check the attached and advise how can this be achieved. Thank you in advance.

data for question.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Value1	Value2	Inv	Price			Value1	Value2	Inv	Price
2	ABC	GHI	5	27.94			ABC	ABC	5	27.94
3	GHI	None	4	55.94			GHI	GHI	4	55.94
4	DMO	PPM	4	55.94			PQS	PQS	2	84.94
5	PQS	AGI	2	84.94			AGI	AGI	5	84.94
6	TTP	DGS	1	84.94
7	AGI	PQS	5	84.94
8	TOF	ABC	6	84.94
9
10
11	ORIGINAL DATA						DESIRED RESULT
12.11.20.dups (1)

 

[Peter_SSs]

Does this do what you want?

20 12 12.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Value1	Value2	Inv	Price			Value1	Value2	Inv	Price
2	ABC	GHI	5	27.94			ABC	ABC	5	27.94
3	GHI	None	4	55.94			GHI	GHI	4	55.94
4	DMO	PPM	4	55.94			PQS	PQS	2	84.94
5	PQS	AGI	2	84.94			AGI	AGI	5	84.94
6	TTP	DGS	1	84.94
7	AGI	PQS	5	84.94
8	TOF	ABC	6	84.94
Match columns

Cell Formulas
Range	Formula
G2:J5	G2	=FILTER(INDEX(A2:D8,SEQUENCE(ROWS(A2:D8)),{1,1,3,4}),ISNUMBER(MATCH(A2:A8,B2:B8,0)))
Dynamic array formulas.

 

[Trebor76]

Hi aghaffar82,

Try this:

Option Explicit
Sub Macro1()

    Dim cln As New Collection
    Dim lngLastRow As Long, lngMyRow As Long, lngPasteRow As Long
    Dim varMyCol As Variant
    Dim strMyValues() As String
    
    Application.ScreenUpdating = False
    
    lngLastRow = Range("A:D").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lngPasteRow = Range("G:J").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    
    For Each varMyCol In Array("A", "B")
        For lngMyRow = 2 To lngLastRow
            On Error Resume Next
                cln.Add Range("C" & lngMyRow) & "|" & Range("D" & lngMyRow), CStr(Range(varMyCol & lngMyRow))
                If Err.Number <> 0 Then
                    Range("G" & lngPasteRow & ":H" & lngPasteRow).Value = CStr(Range(varMyCol & lngMyRow))
                    strMyValues = Split(cln.Item(Range(varMyCol & lngMyRow)), "|")
                    Range("I" & lngPasteRow).Value = CDbl(strMyValues(0))
                    Range("J" & lngPasteRow).Value = CDbl(strMyValues(1))
                    lngPasteRow = lngPasteRow + 1
                End If
            On Error GoTo 0
        Next lngMyRow
    Next varMyCol
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[aghaffar82]

Thank you so much Sir, I will test both of the solutions and update which one runs quicker, I am certain both the solutions are perfect but as the data would be very long, (thousands of rows) I would prefer the one which runs faster and Excel doesn't crash. Thank you again.

 

[aghaffar82]

Does this do what you want?

20 12 12.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Value1	Value2	Inv	Price			Value1	Value2	Inv	Price
2	ABC	GHI	5	27.94			ABC	ABC	5	27.94
3	GHI	None	4	55.94			GHI	GHI	4	55.94
4	DMO	PPM	4	55.94			PQS	PQS	2	84.94
5	PQS	AGI	2	84.94			AGI	AGI	5	84.94
6	TTP	DGS	1	84.94
7	AGI	PQS	5	84.94
8	TOF	ABC	6	84.94
Match columns

Cell Formulas
Range	Formula
G2:J5	G2	=FILTER(INDEX(A2:D8,SEQUENCE(ROWS(A2:D8)),{1,1,3,4}),ISNUMBER(MATCH(A2:A8,B2:B8,0)))
Dynamic array formulas.

Sir, could you please tweak the formula a bit, I want to match the value of H2 and then pull the corresponding value from column C & D into I & J. So, technically, the lookup value remains the same but as the values in column C & D would differ based on the lookup table row. So, if I were using VLOOKUP, I would use it like this in I2:
=VLOOKUP(H2,B:D,2,FLASE) and (for price column J) in J2 =VLOOKUP(H2,B:D,3,FLASE).

Hopefully, that makes sense.

 

[Peter_SSs]

Hopefully, that makes sense.

Not really since your post showed original data in columns B:D how could it look up something from column H?

if I were using VLOOKUP, I would use it like this in I2

If that would work for you, why not use VLOOKUP?

 

[aghaffar82]

Thank you, I wanted to stay dynamic and was able to create another array formula for I & J
=FILTER(C:D,ISNUMBER(MATCH(B:B,H:H,0)),"No data")

The issue with VLOOKUP was it wouldn't update automatically for the new rows.

Best Regards

 

[Peter_SSs]

Thank you, I wanted to stay dynamic and was able to create another array formula for I & J
=FILTER(C:D,ISNUMBER(MATCH(B:B,H:H,0)),"No data")

That does not produce the results you said you wanted?

20 12 12.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Value1	Value2	Inv	Price			Value1	Value2	Inv	Price		Value1	Value2	Inv	Price
2	ABC	GHI	5	27.94				ABC	5	27.94		ABC	ABC	5	27.94
3	GHI	None	4	55.94				GHI	2	84.94		GHI	GHI	4	55.94
4	DMO	PPM	4	55.94				PQS	5	84.94		PQS	PQS	2	84.94
5	PQS	AGI	2	84.94				AGI	6	84.94		AGI	AGI	5	84.94
6	TTP	DGS	1	84.94
7	AGI	PQS	5	84.94
8	TOF	ABC	6	84.94								DESIRED RESULT
Match columns

Cell Formulas
Range	Formula
I1:J5	I1	=FILTER(C:D,ISNUMBER(MATCH(B:B,H:H,0)),"No data")
Dynamic array formulas.

I would also highly recommend not to use whole column references in a formula like that. On my machine that formula takes about 70 times as long to calculate as say

=FILTER(C1:D1000,ISNUMBER(MATCH(B1:B1000,H1:H1000,0)),"No data")

 

[aghaffar82]

Yes, I double-checked and you are absolutely right it is not producing the result I want.

My aim is to match the value of the item column (column B and then pull the inv from column C & Price from column D into the columns N & O). And I don't want to use the VLOOKUP. If I match the values in column A, the corresponding rows have different numbers in Inv & Price columns as those rows would have different item numbers.

Secondly, although the formula doesn't produce the results as desired, it doesn't take more than a few seconds to populate. The reason I have to use the whole column is that the number of rows to be inserted in columns A to D are unknown often and can go way beyond 100k sometimes.

Thank you so much for looking at it again and pointing out the wrong results. Regards

 

[Peter_SSs]

Matching column A rather than column B seems to produce the results you gave before?

20 12 12.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Value1	Value2	Inv	Price			Value1	Value2	Inv	Price
2	ABC	GHI	5	27.94				ABC	5	27.94
3	GHI	None	4	55.94				GHI	4	55.94
4	DMO	PPM	4	55.94				PQS	2	84.94
5	PQS	AGI	2	84.94				AGI	5	84.94
6	TTP	DGS	1	84.94
7	AGI	PQS	5	84.94
8	TOF	ABC	6	84.94
Match columns

Cell Formulas
Range	Formula
I2:J5	I2	=FILTER(C:D,ISNUMBER(MATCH(A:A,H:H,0)),"No data")
Dynamic array formulas.