Matching Lists

L

LearnMeExcel

Well-known Member
Joined
Aug 11, 2009
Messages
746
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
hi
i want formula or macro, to match the list one with list two
but the problem in list one there is no desription for invoice number the only criteria is invoice amount
i want to make this for list one to give me in Column D "Match" if it found it and "Not Match" if it is not.
the same thing for list two
but some number duplicate
for exampl nomber
75000 in list one found 4 times while it is 3 time, the result for first 3 is
75000 Match
75000 Match
75000 Match
75000 Not Match
i hope it is calear, and thanx in advanced for any help.


Excel 2010
ABCDEFGHI
1List 1List 2
2
3DateDescriptionValueDateDescriptionValue
401/12/201475,000.0001/12/2014Invoice 674675,000.00
501/12/201425,000.0001/12/2014Invoice 343525,000.00
602/12/201437,500.0002/12/2014Invoice 508837,500.00
703/12/201485,250.0003/12/2014Invoice 906097,500.00
804/12/201425,000.0004/12/2014Invoice 992925,000.00
905/12/201425,000.0005/12/2014Invoice 867625,000.00
1006/12/2014100,000.0006/12/2014Invoice 1296100,000.00
1107/12/201435,500.0007/12/2014Invoice 295135,500.00
1208/12/201459,750.0008/12/2014Invoice 176359,750.00
1309/12/201413,250.0009/12/2014Invoice 134113,250.00
1410/12/201475,000.0010/12/2014Invoice 916475,500.00
1511/12/201445,500.0011/12/2014Invoice 895245,500.00
1612/12/201475,000.0012/12/2014Invoice 958275,000.00
1713/12/201473,250.0013/12/2014Invoice 963473,250.00
1814/12/201425,500.0014/12/2014Invoice 725825,500.00
1915/12/201494,500.0015/12/2014Invoice 554994,500.00
2016/12/201435,500.0015/12/2014Invoice 376335,500.00
2117/12/201475,000.0015/12/2014Invoice 415075,000.00
2218/12/201454,000.0015/12/2014Invoice 152454,000.00
2315/12/2014Invoice 751792,750.00
2416/12/2014Invoice 812257,500.00
2516/12/2014Invoice 362073,750.00
2617/12/2014Invoice 223125,000.00
2717/12/2014Invoice 957850,250.00
2818/12/2014Invoice 250178,750.00
Sheet1
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
This is my solution
let me know!

<table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:14pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="width:30px; " /><col style="width:98.4px;" /><col style="width:143.2px;" /><col style="width:93.6px;" /><col style="width:52px;" /><col style="width:98.4px;" /><col style="width:172px;" /><col style="width:93.6px;" /></colgroup><tr style="background-color:#cacaca; text-align:center;font-size:8pt; "><td > </td><td style=" border-width:3px; border-style:solid;border-color:#f0f0f0; ">A</td><td style=" border-width:3px; border-style:solid;border-color:#f0f0f0; ">B</td><td style=" border-width:3px; border-style:solid;border-color:#f0f0f0; ">C</td><td style=" border-width:3px; border-style:solid;border-color:#f0f0f0; ">D</td><td style=" border-width:3px; border-style:solid;border-color:#f0f0f0; ">E</td><td style=" border-width:3px; border-style:solid;border-color:#f0f0f0; ">F</td><td style=" border-width:3px; border-style:solid;border-color:#f0f0f0; ">G</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >1</td><td style="color:#ff0000; ">List1</td><td > </td><td > </td><td > </td><td style="color:#ff0000; ">List 2</td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#ffff00; ">Date</td><td style="background-color:#ffff00; ">Description</td><td style="background-color:#ffff00; ">Value</td><td > </td><td style="background-color:#ff0000; color:#ffffff; ">Date</td><td style="background-color:#ff0000; color:#ffffff; ">Description</td><td style="background-color:#ff0000; color:#ffffff; ">Value</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">01/12/2014</td><td >Invoice 6746</td><td style="text-align:right; ">75,000.00</td><td > </td><td style="text-align:right; ">01/12/2014</td><td >Invoice 6746</td><td style="text-align:right; ">75,000.00</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">01/12/2014</td><td >Invoice 3435</td><td style="text-align:right; ">25,000.00</td><td > </td><td style="text-align:right; ">01/12/2014</td><td >Invoice 3435</td><td style="text-align:right; ">25,000.00</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">02/12/2014</td><td >Invoice 5088</td><td style="text-align:right; ">37,500.00</td><td > </td><td style="text-align:right; ">02/12/2014</td><td >Invoice 5088</td><td style="text-align:right; ">37,500.00</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">03/12/2014</td><td >NOR AVAILABLE</td><td style="text-align:right; ">85,250.00</td><td > </td><td style="text-align:right; ">03/12/2014</td><td >Invoice 9060</td><td style="text-align:right; ">97,500.00</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">04/12/2014</td><td >Invoice 9929</td><td style="text-align:right; ">25,000.00</td><td > </td><td style="text-align:right; ">04/12/2014</td><td >Invoice 9929</td><td style="text-align:right; ">25,000.00</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">05/12/2014</td><td >Invoice 8676</td><td style="text-align:right; ">25,000.00</td><td > </td><td style="text-align:right; ">05/12/2014</td><td >Invoice 8676</td><td style="text-align:right; ">25,000.00</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">06/12/2014</td><td >Invoice 1296</td><td style="text-align:right; ">100,000.00</td><td > </td><td style="text-align:right; ">06/12/2014</td><td >Invoice 1296</td><td style="text-align:right; ">100,000.00</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">07/12/2014</td><td >Invoice 2951</td><td style="text-align:right; ">35,500.00</td><td > </td><td style="text-align:right; ">07/12/2014</td><td >Invoice 2951</td><td style="text-align:right; ">35,500.00</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">08/12/2014</td><td >Invoice 1763</td><td style="text-align:right; ">59,750.00</td><td > </td><td style="text-align:right; ">08/12/2014</td><td >Invoice 1763</td><td style="text-align:right; ">59,750.00</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">09/12/2014</td><td >Invoice 1341</td><td style="text-align:right; ">13,250.00</td><td > </td><td style="text-align:right; ">09/12/2014</td><td >Invoice 1341</td><td style="text-align:right; ">13,250.00</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">10/12/2014</td><td >Invoice 9582</td><td style="text-align:right; ">75,000.00</td><td > </td><td style="text-align:right; ">10/12/2014</td><td >Invoice 9164</td><td style="text-align:right; ">75,500.00</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">11/12/2014</td><td >Invoice 8952</td><td style="text-align:right; ">45,500.00</td><td > </td><td style="text-align:right; ">11/12/2014</td><td >Invoice 8952</td><td style="text-align:right; ">45,500.00</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">12/12/2014</td><td >Invoice 4150</td><td style="text-align:right; ">75,000.00</td><td > </td><td style="text-align:right; ">12/12/2014</td><td >Invoice 9582</td><td style="text-align:right; ">75,000.00</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">13/12/2014</td><td >Invoice 9634</td><td style="text-align:right; ">73,250.00</td><td > </td><td style="text-align:right; ">13/12/2014</td><td >Invoice 9634</td><td style="text-align:right; ">73,250.00</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">14/12/2014</td><td >Invoice 7258</td><td style="text-align:right; ">25,500.00</td><td > </td><td style="text-align:right; ">14/12/2014</td><td >Invoice 7258</td><td style="text-align:right; ">25,500.00</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >18</td><td style="text-align:right; ">15/12/2014</td><td >Invoice 5549</td><td style="text-align:right; ">94,500.00</td><td > </td><td style="text-align:right; ">15/12/2014</td><td >Invoice 5549</td><td style="text-align:right; ">94,500.00</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >19</td><td style="text-align:right; ">16/12/2014</td><td >Invoice 3763</td><td style="text-align:right; ">35,500.00</td><td > </td><td style="text-align:right; ">15/12/2014</td><td >Invoice 3763</td><td style="text-align:right; ">35,500.00</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >20</td><td style="text-align:right; ">17/12/2014</td><td >NOR AVAILABLE</td><td style="text-align:right; ">75,000.00</td><td > </td><td style="text-align:right; ">15/12/2014</td><td >Invoice 4150</td><td style="text-align:right; ">75,000.00</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >21</td><td style="text-align:right; ">18/12/2014</td><td >Invoice 1524</td><td style="text-align:right; ">54,000.00</td><td > </td><td style="text-align:right; ">15/12/2014</td><td >Invoice 1524</td><td style="text-align:right; ">54,000.00</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >22</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">15/12/2014</td><td >Invoice 7517</td><td style="text-align:right; ">92,750.00</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >23</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">16/12/2014</td><td >Invoice 8122</td><td style="text-align:right; ">57,500.00</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >24</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">16/12/2014</td><td >Invoice 3620</td><td style="text-align:right; ">73,750.00</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >25</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">17/12/2014</td><td >Invoice 2231</td><td style="text-align:right; ">25,000.00</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >26</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">17/12/2014</td><td >Invoice 9578</td><td style="text-align:right; ">50,250.00</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >27</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">18/12/2014</td><td >Invoice 2501</td><td style="text-align:right; ">78,750.00</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B3</td><td >{=IFERROR(INDEX<span style=' color:008000; '>($F$3:$F$27,SMALL<span style=' color:#0000ff; '>(IF<span style=' color:#ff0000; '>($G$3:$G$27=C3,ROW<span style=' color:#804000; '>($G$3:$G$27)</span>-ROW<span style=' color:#804000; '>($G$3)</span>+1)</span>,COUNTIF<span style=' color:#ff0000; '>($C$3:C3,C3)</span>)</span>)</span>,"NOR AVAILABLE")}</td></tr></table></td></tr><tr><td ><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>Formula Array:</span><span style=' font-family:Arial,Arial; font-size:9pt;'><br />Produce enclosing </span><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>{ }</span><span style=' font-family:Arial,Arial; font-size:9pt;'> by entering<br />formula with CTRL+SHIFT+ENTER!</span></td></tr><tr><td >Matrix verstehen</td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4
 
Upvote 0
Opppss!
I forgot to tell you to press CTRL+SHIFT+ENTER and not only ENTER
let me know!
 
Upvote 0
hi! Always me
If you have Excel 2010 or 2013 you can use this formula

in B3

=IFERROR(INDEX($F$3:$F$27;AGGREGATE(15;6;(ROW($G$3:$G$27)-ROW($G$3)+1)/($G$3:$G$27=C3);COUNTIF($C$3:C3;C3)));"not available")
 
Last edited:
Upvote 0
great thanks friends for this solutions

can we use macro to solve this too, cuz my data more than 40000 rows

thanks again
 
Upvote 0
LearnMeExcel,

can we use macro to solve this too, cuz my data more than 40000 rows
Click to expand...

Here is a macro solution, using VBA Geek's formula, for you to consider.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Sub MatchLists()
' hiker95, 01/11/2015, ME828227
Dim lr As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "A").End(xlUp).Row
With Range("B4:B" & lr)
  .Formula = "=IF(COUNTIF(C$4:C4,C4)<=COUNTIF(I$4:I$28,C4),""Match"",""Not Match"")"
  .Value = .Value
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the MatchLists macro.
 
Upvote 0
LearnMeExcel,

Here is an updated macro for you to consider.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code: 
Sub MatchLists_V2()
' hiker95, 01/11/2015, ME828227
Dim lra As Long, lrg As Long
Application.ScreenUpdating = False
lra = Cells(Rows.Count, "A").End(xlUp).Row
lrg = Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
With Range("B4:B" & lra)
  .Formula = "=IF(COUNTIF(C$4:C4,C4)<=COUNTIF(I$4:I$" & lrg & ",C4),""Match"",""Not Match"")"
  .Value = .Value
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the MatchLists_V2 macro.
 
Upvote 0
LearnMeExcel,

Here is an updated macro for you to consider.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Rich (BB code): 
LearnMeExcel,

Here is an updated macro for you to consider.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


	
	
	
	
	
	




	

		Rich (BB code):

	

	

		
Sub MatchLists_V2()
' hiker95, 01/11/2015, ME828227
Dim lra As Long, lrg As Long
Application.ScreenUpdating = False
lra = Cells(Rows.Count, "A").End(xlUp).Row
lrg = Cells(Rows.Count, "G").End(xlUp).Row
With Range("B4:B" & lra)
  .Formula = "=IF(COUNTIF(C$4:C4,C4)<=COUNTIF(I$4:I$" & lrg & ",C4),""Match"",""Not Match"")"
  .Value = .Value
End With
Application.ScreenUpdating = True
End Sub

	



Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the MatchLists_V2 macro.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the MatchLists_V2 macro.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Help on logical argument w/ Vlookup
Replies
4
Views
224
StephenCrump
S
k3yn0t3
  • Solved
INDEX(MATCH()) pulling wrong row data & Macro to duplicate sheets with formulas
Replies
4
Views
379
Alex Blakenburg
A
C
  • Solved
Return text which appears in one list but not in a second list?
Replies
3
Views
747
Peter_SSs
Peter_SSs
M
  • Solved
ListBox does not display the record I try to find
Replies
9
Views
516
Magic Polygon
M
A
  • Solved
Returning a single result for a match under certain criteria
Replies
5
Views
381
ATHlaptop
A

Forum statistics

Threads
1,214,827
Messages
6,121,816
Members
449,049
Latest member
cybersurfer5000

Share this page

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