Compare 2 columns and find duplicates then copy corresponding cell into another cell

[gray_b]

On the same workpage I have column 'H' of a 1000+ text cells, and another column 'B' of 2000+ text cells.

There are many duplicate text cells in column 'B'. But the corresponding text cells in column 'A' always stays the same in relationship to text cells in column 'B'

I need to go through each cell in turn from column 'H' and find a duplicate text cell in column 'B'.

When a duplicate cell is found, then copy its corresponding cell text from cells in column 'A' and paste into column 'G'.

Then onto the next cell in column 'H'

 

[kevin9999]

Could you please provide a copy of your data using the XL2BB add in so helpers don't have to type out a load of data to test code/formulas - or alternatively share your file via Google Drive, Dropbox or similar file sharing platform? It would also help if you could update your profile to show what version of Excel you're using, as solutions may vary depending on which version you have. Thank you in anticipation.

 

[gray_b]

Hi - Excel 365

Have a look at file here

It is a complete xlsm file, data included is not live data, only demo.

Excuse me for the coding, as i like to keep things simple and understandable in my older years.

 

[kevin9999]

Thank you for that. I'm wondering whether it might be simpler to go for a formula solution rather than VBA? Here's just a suggestion, have a look at the XLOOKUP function in column G in the sample below & see if it is doing what you want. Please let me know if it isn't (I'll look at it tomorrow my time).

extract all products sold and quatify backup new 1.xlsm
	A	B	C	D	E	F	G	H
1	Product Reference	Product Name	Product Quantity	Order ID				Product Name
2		10 x Grafting Package	1	2344			0	10 x Grafting Package
3		10 x Grafting Package	1	2358			0	100 x Grafting Package
4		10 x Grafting Package	1	2398			0	50 x Grafting Package
5		10 x Grafting Package	1	2401			Malus 1705-000	Apple - Acme - Scion
6		10 x Grafting Package	1	2406			Malus 8684	Apple - Adams Pearmain - Barerooted
7		10 x Grafting Package	1	2420			Malus 833-J18	Apple - Adams Pearmain - Scion
8		10 x Grafting Package	1	2427			Malus 3550-I53	Apple - All Doer - Scion
9		100 x Grafting Package	1	2387			Malus 5026-HL29	Apple - Amanda - Scion
10		100 x Grafting Package	1	2451			Malus 1613-J15	Apple - American Mother - Scion
11		50 x Grafting Package	1	2323			Malus 3750-HL11	Apple - Annie Elizabeth - Scion
12	Malus 1705-000	Apple - Acme - Scion	2	2357			Malus 4928-F69	Apple - Arap Kizi 'Arabian Girl' - Scion
13	Malus 1705-000	Apple - Acme - Scion	1	2399			Malus 2962-U10	Apple - Aroma - Scion
14	Malus 8684	Apple - Adams Pearmain - Barerooted	1	2431			Malus 800-D09	Apple - Arthur Turner - Scion
15	Malus 833-J18	Apple - Adams Pearmain - Scion	1	2329			Malus 8773	Apple - Ashmeads Kernel - Barerooted
16	Malus 833-J18	Apple - Adams Pearmain - Scion	1	2334			Malus 3616-U29	Apple - Ashmeads Kernel - Scion
17	Malus 833-J18	Apple - Adams Pearmain - Scion	1	2354			Malus 5641-X30	Apple - Autumn Arctic - Scion
18	Malus 833-J18	Apple - Adams Pearmain - Scion	1	2356			Malus 1072-L11	Apple - Bakers Delicious - Scion
19	Malus 833-J18	Apple - Adams Pearmain - Scion	1	2384			Malus 4900-W7	Apple - Bakran - Scion
20	Malus 833-J18	Apple - Adams Pearmain - Scion	1	2416			Malus 2818-Q09	Apple - Bardsey - Scion
21	Malus 833-J18	Apple - Adams Pearmain - Scion	1	2417			Malus 3749-HL07	Apple - Barnack Orange - Scion
22	Malus 833-J18	Apple - Adams Pearmain - Scion	1	2423			Malus 5328-W25	Apple - Batul - Scion
23	Malus 833-J18	Apple - Adams Pearmain - Scion	2	2428			Malus 1704-O17	Apple - Beauty of Bath - Scion
24	Malus 3550-I53	Apple - All Doer - Scion	1	2399			Malus 1746-P08	Apple - Bec d Oie - Scion
25	Malus 3550-I53	Apple - All Doer - Scion	1	2430			Malus 1718-P04	Apple - Benoni - Scion
26	Malus 5026-HL29	Apple - Amanda - Scion	1	2356			Malus 1021-Q38	Apple - Ben's Red - Scion
27	Malus 5026-HL29	Apple - Amanda - Scion	1	2467			Malus 1707-O21	Apple - Blood Royal - Scion
28	Malus 1613-J15	Apple - American Mother - Scion	1	2462			Malus 2285-BW32	Apple - Bloody Ploughman - Scion
29	Malus 3750-HL11	Apple - Annie Elizabeth - Scion	1	2363			Malus 1235-J39	Apple - Braeburn - Scion
30	Malus 4928-F69	Apple - Arap Kizi 'Arabian Girl' - Scion	1	2350			Malus 792-D1	Apple - Bramley - Scion
31	Malus 4928-F69	Apple - Arap Kizi 'Arabian Girl' - Scion	1	2399			Malus 1086-J28	Apple - Bramley Clone 20 - Scion
export_orders_2023.02.26_8-59-1

Cell Formulas
Range	Formula
G2:G31	G2	=XLOOKUP(H2,$B$2:$B$1011,$A$2:$A$1011,"Not found",0,1)

 

[gray_b]

It certainly does the job.

I must admit that I have never used XLOOKUP but always VBA

I might be showing my incompetence, but can that formula be incorporated in VBA. As I do not know what the cell count is, as it can change.

and i use LRC = Cells(Rows.Count, "C").End(xlUp).Row

 

[kevin9999]

It can be added via VBA, I'm away from my laptop now but I will get back to you tomorrow, unless another volunteer steps in beforehand.

 

[kevin9999]

Here's some code to insert the Xlookup formula & values. I didn't hard code the sheet name as I suspect it might change - therefore, you must run the code with the export_orders sheet active.

Option Explicit
Sub Get_XLOOKUP()
    Dim LRowB As Long, LRowH As Long
    LRowB = Cells(Rows.Count, "B").End(xlUp).Row
    LRowH = Cells(Rows.Count, "H").End(xlUp).Row

    With Range("G2:G" & LRowH)
        .Formula = "=XLOOKUP(H2,$B$2:$B$" & LRowB & ",$A$2:$A$" & LRowB & ",""Not found"",0,1)"
        .Value = .Value
    End With
End Sub

 

[gray_b]

Excellent - I think I was no where near close with the code. And I did not put any " around the XLOOKUP statement.

Copy and pasted your code, and worked first time.

Many thanks, as I would never have sussed it out.

 

[kevin9999]

Happy to help, and thanks for the feedback