vba code to find duplicates in two lists

helpsky

New Member
Joined
May 13, 2018
Messages
7
hi!

i have two lists below, one is my inventory and one is what i want to match it against:

inventory list
[TABLE="width: 500"]
<tbody>[TR]
[TD]Ticker[/TD]
[TD]Quantity[/TD]
[TD]Counterparty[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[TD]1[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[TD]2[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]BABA[/TD]
[TD]1[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]T[/TD]
[TD]3[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]T[/TD]
[TD]4[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]CIS[/TD]
[TD]3[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]NFLX[/TD]
[TD]4[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]NFLX[/TD]
[TD]4[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]NFLX[/TD]
[TD]7[/TD]
[TD]G[/TD]
[/TR]
</tbody>[/TABLE]

match to:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Ticker[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]T[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]NFLX[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]NFLX[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]


IM not sure if its possible to write a code for this but... i would like a way to see the duplicate value on the two lists under the ticker columns, meaning if the inventory list has AAPL and the match list has AAPL then AAPL will end u in the result column BUT it will end up there the exact amount of times that it appears on each list so AAPL is on the first list twice and on the second list 2 times so it comes up twice under matches in results and 3 times under needed in the results. the only criteria that needs to be matches is the tickers but i am having difficulty finding ways to align the quantity and counterparty since the amounts can differ on each list. please let me know if you can help with a code for this or a better way. thank you!!!


RESULT:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Matches[/TD]
[TD]Quantity[/TD]
[TD]Counterparty[/TD]
[TD]Needed[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[TD]1[/TD]
[TD]A[/TD]
[TD]AAPL[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[TD]2[/TD]
[TD]B[/TD]
[TD]AAPL[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]AAPL[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]T[/TD]
[TD]3[/TD]
[TD]A[/TD]
[TD]T[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]T[/TD]
[TD]4[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NFLX[/TD]
[TD]4[/TD]
[TD]G[/TD]
[TD]NFLX[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]NFLX[/TD]
[TD]4[/TD]
[TD]H[/TD]
[TD]NFLX[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]NFLX[/TD]
[TD]7[/TD]
[TD]G[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Forum statistics

Threads
1,223,911
Messages
6,175,334
Members
452,636
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top