Formula to transfer multiple rows of entries with reference

JDJong

New Member
Joined
May 2, 2018
Messages
39
Hello everyone,

I am stuck with tranferring the spreadsheet i receive to a new workbook by having a better overview of each invoice.


I would like to have the data from table 1 Transfer to table 2 with certain lookup Aggregate formula?
same invoice number, Company, Serial number, tax rate and Performance date may appear multiple times due to the different orders.

in table 2 I would like to have the invoice number, Performance(Company); Description(Serial number), tax rate, currency; Performance date appear only once; but to sum the invoice amount and tax amount.

Results will be like this:
Invoice numberPerformerDescrpitionTax rateInvoice amountTax AmountCurrencyPerformance date
PIN100722SweptoffPIN2007700,0018.877,000,00USD 03-jan-2018
PIN100732Eastern cleaningPIN20077323,0024.500,004.655,00USD 04-jan-2018
PIN100737DNSPIN200788010.700,000USD 12-jan-2018

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col></colgroup>




Table 1
Invoice numberCompanySerial numberOrder numberTax rateInvoice amountTax AmountCurrencyPerformance date
PIN100722SweptoffPIN2007705A14330,0018.877,000,00USD 03-jan-2018
PIN100732Eastern cleaningPIN2007735A188523,0024.000,004.560,00USD 03-jan-2018
PIN100732Eastern cleaningPIN200773154DN223,00500,0095,00USD 03-jan-2018
PIN100733Western cleaningPIN2007796A816223,00650.000,00123.500,00USD 03-jan-2018
PIN100734Southern cleaningPIN2007552A41570,0016.950,000,00USD 08-jan-2018
PIN100735Northern CleaningPIN2007849379M61P0323,001.292,50245,58USD 08-jan-2018
PIN100736OrientalPIN200785340-051-901-00,004.740,000,00USD 12-jan-2018
PIN100737DNSPIN2007881347M32G080,002.850,000,00USD 12-jan-2018
PIN100737DNSPIN2007883A27040,005.000,000,00USD 12-jan-2018
PIN100737DNSPIN2007886A79060,002.850,000,00USD 12-jan-2018
PIN100738KEAPIN200789340-085-120-00,0021.000,000,00USD 12-jan-2018
PIN100739KLCPIN2007871523M71G070,002.850,000,00USD 12-jan-2018
PIN100739KLCPIN2007871851M59P010,002.850,000,00USD 12-jan-2018
PIN100739KLCPIN2007871864M97P010,002.850,000,00USD 12-jan-2018
PIN100740874EAPPIN2007921971M17G010,005.000,000,00USD 18-jan-2018
PIN100741DLCPIN200797340-116-401-00,0018.000,000,00USD 19-jan-2018
PIN100742COPNPIN2008029511M24P070,007.950,000,00USD 22-jan-2018



<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col></colgroup>
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,202
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
try

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Results will be like this:</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Invoice number</td><td style=";">Performer</td><td style=";">Descrpition</td><td style=";">Tax rate</td><td style=";">Invoice amount</td><td style=";">Tax Amount</td><td style=";">Currency</td><td style=";">Performance date</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="background-color: #E2EFDA;;">PIN100722</td><td style="background-color: #E2EFDA;;">Sweptoff</td><td style="background-color: #E2EFDA;;">PIN200770</td><td style="text-align: right;background-color: #E2EFDA;;">0,00</td><td style="text-align: right;background-color: #E2EFDA;;">18,877.00</td><td style="text-align: right;background-color: #E2EFDA;;">0.00</td><td style="background-color: #E2EFDA;;">USD</td><td style="text-align: right;background-color: #E2EFDA;;">03-Jan-18</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">PIN100732</td><td style=";">Eastern cleaning</td><td style=";">PIN200773</td><td style="text-align: right;;">23,00</td><td style="text-align: right;;">24,500.00</td><td style="text-align: right;;">4,655.00</td><td style=";">USD</td><td style="text-align: right;;">03-Jan-18</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">PIN100733</td><td style=";">Western cleaning</td><td style=";">PIN200779</td><td style="text-align: right;;">23,00</td><td style="text-align: right;;">650,000.00</td><td style="text-align: right;;">123,500.00</td><td style=";">USD</td><td style="text-align: right;;">03-Jan-18</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">PIN100734</td><td style=";">Southern cleaning</td><td style=";">PIN200755</td><td style="text-align: right;;">0,00</td><td style="text-align: right;;">16,950.00</td><td style="text-align: right;;">0.00</td><td style=";">USD</td><td style="text-align: right;;">08-Jan-18</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">PIN100735</td><td style=";">Northern Cleaning</td><td style=";">PIN200784</td><td style="text-align: right;;">23,00</td><td style="text-align: right;;">1,292.50</td><td style="text-align: right;;">245.58</td><td style=";">USD</td><td style="text-align: right;;">08-Jan-18</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">PIN100736</td><td style=";">Oriental</td><td style=";">PIN200785</td><td style="text-align: right;;">0,00</td><td style="text-align: right;;">4,740.00</td><td style="text-align: right;;">0.00</td><td style=";">USD</td><td style="text-align: right;;">12-Jan-18</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">PIN100737</td><td style=";">DNS</td><td style=";">PIN200788</td><td style="text-align: right;;">0,00</td><td style="text-align: right;;">10,700.00</td><td style="text-align: right;;">0.00</td><td style=";">USD</td><td style="text-align: right;;">12-Jan-18</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Table 1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Invoice number</td><td style=";">Company</td><td style=";">Serial number</td><td style=";">Order number</td><td style=";">Tax rate</td><td style=";">Invoice amount</td><td style=";">Tax Amount</td><td style=";">Currency</td><td style=";">Performance date</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">PIN100722</td><td style=";">Sweptoff</td><td style=";">PIN200770</td><td style=";">5A1433</td><td style="text-align: right;;">0,00</td><td style="text-align: right;;">18,877.00</td><td style="text-align: right;;">0.00</td><td style=";">USD</td><td style="text-align: right;;">03-Jan-18</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">PIN100732</td><td style=";">Eastern cleaning</td><td style=";">PIN200773</td><td style=";">5A1885</td><td style="text-align: right;;">23,00</td><td style="text-align: right;;">24,000.00</td><td style="text-align: right;;">4,560.00</td><td style=";">USD</td><td style="text-align: right;;">03-Jan-18</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">PIN100732</td><td style=";">Eastern cleaning</td><td style=";">PIN200773</td><td style=";">154DN2</td><td style="text-align: right;;">23,00</td><td style="text-align: right;;">500.00</td><td style="text-align: right;;">95.00</td><td style=";">USD</td><td style="text-align: right;;">03-Jan-18</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">PIN100733</td><td style=";">Western cleaning</td><td style=";">PIN200779</td><td style=";">6A8162</td><td style="text-align: right;;">23,00</td><td style="text-align: right;;">650,000.00</td><td style="text-align: right;;">123,500.00</td><td style=";">USD</td><td style="text-align: right;;">03-Jan-18</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">PIN100734</td><td style=";">Southern cleaning</td><td style=";">PIN200755</td><td style=";">2A4157</td><td style="text-align: right;;">0,00</td><td style="text-align: right;;">16,950.00</td><td style="text-align: right;;">0.00</td><td style=";">USD</td><td style="text-align: right;;">08-Jan-18</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">PIN100735</td><td style=";">Northern Cleaning</td><td style=";">PIN200784</td><td style=";">9379M61P03</td><td style="text-align: right;;">23,00</td><td style="text-align: right;;">1,292.50</td><td style="text-align: right;;">245.58</td><td style=";">USD</td><td style="text-align: right;;">08-Jan-18</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">PIN100736</td><td style=";">Oriental</td><td style=";">PIN200785</td><td style=";">340-051-901-0</td><td style="text-align: right;;">0,00</td><td style="text-align: right;;">4,740.00</td><td style="text-align: right;;">0.00</td><td style=";">USD</td><td style="text-align: right;;">12-Jan-18</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">PIN100737</td><td style=";">DNS</td><td style=";">PIN200788</td><td style=";">1347M32G08</td><td style="text-align: right;;">0,00</td><td style="text-align: right;;">2,850.00</td><td style="text-align: right;;">0.00</td><td style=";">USD</td><td style="text-align: right;;">12-Jan-18</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style=";">PIN100737</td><td style=";">DNS</td><td style=";">PIN200788</td><td style=";">3A2704</td><td style="text-align: right;;">0,00</td><td style="text-align: right;;">5,000.00</td><td style="text-align: right;;">0.00</td><td style=";">USD</td><td style="text-align: right;;">12-Jan-18</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style=";">PIN100737</td><td style=";">DNS</td><td style=";">PIN200788</td><td style=";">6A7906</td><td style="text-align: right;;">0,00</td><td style="text-align: right;;">2,850.00</td><td style="text-align: right;;">0.00</td><td style=";">USD</td><td style="text-align: right;;">12-Jan-18</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style=";">PIN100738</td><td style=";">KEA</td><td style=";">PIN200789</td><td style=";">340-085-120-0</td><td style="text-align: right;;">0,00</td><td style="text-align: right;;">21,000.00</td><td style="text-align: right;;">0.00</td><td style=";">USD</td><td style="text-align: right;;">12-Jan-18</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style=";">PIN100739</td><td style=";">KLC</td><td style=";">PIN200787</td><td style=";">1523M71G07</td><td style="text-align: right;;">0,00</td><td style="text-align: right;;">2,850.00</td><td style="text-align: right;;">0.00</td><td style=";">USD</td><td style="text-align: right;;">12-Jan-18</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">25</td><td style=";">PIN100739</td><td style=";">KLC</td><td style=";">PIN200787</td><td style=";">1851M59P01</td><td style="text-align: right;;">0,00</td><td style="text-align: right;;">2,850.00</td><td style="text-align: right;;">0.00</td><td style=";">USD</td><td style="text-align: right;;">12-Jan-18</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">26</td><td style=";">PIN100739</td><td style=";">KLC</td><td style=";">PIN200787</td><td style=";">1864M97P01</td><td style="text-align: right;;">0,00</td><td style="text-align: right;;">2,850.00</td><td style="text-align: right;;">0.00</td><td style=";">USD</td><td style="text-align: right;;">12-Jan-18</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">27</td><td style=";">PIN100740</td><td style=";">874EAP</td><td style=";">PIN200792</td><td style=";">1971M17G01</td><td style="text-align: right;;">0,00</td><td style="text-align: right;;">5,000.00</td><td style="text-align: right;;">0.00</td><td style=";">USD</td><td style="text-align: right;;">18-Jan-18</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">28</td><td style=";">PIN100741</td><td style=";">DLC</td><td style=";">PIN200797</td><td style=";">340-116-401-0</td><td style="text-align: right;;">0,00</td><td style="text-align: right;;">18,000.00</td><td style="text-align: right;;">0.00</td><td style=";">USD</td><td style="text-align: right;;">19-Jan-18</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">29</td><td style=";">PIN100742</td><td style=";">COPN</td><td style=";">PIN200802</td><td style=";">9511M24P07</td><td style="text-align: right;;">0,00</td><td style="text-align: right;;">7,950.00</td><td style="text-align: right;;">0.00</td><td style=";">USD</td><td style="text-align: right;;">22-Jan-18</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet6</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B3</th><td style="text-align:left">=LOOKUP(<font color="Blue">A3,$A$13:$A$29,$B$13:$B$29</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C3</th><td style="text-align:left">=LOOKUP(<font color="Blue">A3,$A$13:$A$29,$C$13:$C$29</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D3</th><td style="text-align:left">=LOOKUP(<font color="Blue">A3,$A$13:$A$29,$E$13:$E$29</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E3</th><td style="text-align:left">=SUMIF(<font color="Blue">$A$13:$A$29,A3,$F$13:$F$29</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F3</th><td style="text-align:left">=SUMIF(<font color="Blue">$A$13:$A$29,A3,$G$13:$G$29</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G3</th><td style="text-align:left">=LOOKUP(<font color="Blue">A3,$A$13:$A$29,$H$13:$H$29</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H3</th><td style="text-align:left">=LOOKUP(<font color="Blue">A3,$A$13:$A$29,$I$13:$I$29</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A3</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">$A$13:$A$29, MATCH(<font color="Green">0, COUNTIF(<font color="Purple">$A$2:A2, $A$13:$A$29</font>), 0</font>)</font>),""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

JDJong

New Member
Joined
May 2, 2018
Messages
39
try

Excel 2013/2016
ABCDEFGHI
1Results will be like this:
2Invoice numberPerformerDescrpitionTax rateInvoice amountTax AmountCurrencyPerformance date
3PIN100722SweptoffPIN2007700,0018,877.000.00USD03-Jan-18
4PIN100732Eastern cleaningPIN20077323,0024,500.004,655.00USD03-Jan-18
5PIN100733Western cleaningPIN20077923,00650,000.00123,500.00USD03-Jan-18
6PIN100734Southern cleaningPIN2007550,0016,950.000.00USD08-Jan-18
7PIN100735Northern CleaningPIN20078423,001,292.50245.58USD08-Jan-18
8PIN100736OrientalPIN2007850,004,740.000.00USD12-Jan-18
9PIN100737DNSPIN2007880,0010,700.000.00USD12-Jan-18
10
11Table 1
12Invoice numberCompanySerial numberOrder numberTax rateInvoice amountTax AmountCurrencyPerformance date
13PIN100722SweptoffPIN2007705A14330,0018,877.000.00USD03-Jan-18
14PIN100732Eastern cleaningPIN2007735A188523,0024,000.004,560.00USD03-Jan-18
15PIN100732Eastern cleaningPIN200773154DN223,00500.0095.00USD03-Jan-18
16PIN100733Western cleaningPIN2007796A816223,00650,000.00123,500.00USD03-Jan-18
17PIN100734Southern cleaningPIN2007552A41570,0016,950.000.00USD08-Jan-18
18PIN100735Northern CleaningPIN2007849379M61P0323,001,292.50245.58USD08-Jan-18
19PIN100736OrientalPIN200785340-051-901-00,004,740.000.00USD12-Jan-18
20PIN100737DNSPIN2007881347M32G080,002,850.000.00USD12-Jan-18
21PIN100737DNSPIN2007883A27040,005,000.000.00USD12-Jan-18
22PIN100737DNSPIN2007886A79060,002,850.000.00USD12-Jan-18
23PIN100738KEAPIN200789340-085-120-00,0021,000.000.00USD12-Jan-18
24PIN100739KLCPIN2007871523M71G070,002,850.000.00USD12-Jan-18
25PIN100739KLCPIN2007871851M59P010,002,850.000.00USD12-Jan-18
26PIN100739KLCPIN2007871864M97P010,002,850.000.00USD12-Jan-18
27PIN100740874EAPPIN2007921971M17G010,005,000.000.00USD18-Jan-18
28PIN100741DLCPIN200797340-116-401-00,0018,000.000.00USD19-Jan-18
29PIN100742COPNPIN2008029511M24P070,007,950.000.00USD22-Jan-18

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet6

Worksheet Formulas
CellFormula
B3=LOOKUP(A3,$A$13:$A$29,$B$13:$B$29)
C3=LOOKUP(A3,$A$13:$A$29,$C$13:$C$29)
D3=LOOKUP(A3,$A$13:$A$29,$E$13:$E$29)
E3=SUMIF($A$13:$A$29,A3,$F$13:$F$29)
F3=SUMIF($A$13:$A$29,A3,$G$13:$G$29)
G3=LOOKUP(A3,$A$13:$A$29,$H$13:$H$29)
H3=LOOKUP(A3,$A$13:$A$29,$I$13:$I$29)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
A3{=IFERROR(INDEX($A$13:$A$29, MATCH(0, COUNTIF($A$2:A2, $A$13:$A$29), 0)),"")}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>


Hi Alan,

Thanks a lot for your prompt Response.

However, in column A, if i drag it down, the invoice number doesn't auto-populate.

is there a way to drag it down and to copy the whole list ?
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,202
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
it should, as it's as array formula you need to enter the formula without the {} brackets and confirm with shift-Control-enter all together
 
Last edited:

JDJong

New Member
Joined
May 2, 2018
Messages
39

ADVERTISEMENT

it should, as it's as array formula you need to enter the formula without the {} brackets and confirm with shift-Control-enter all together

I copied it down... but all Returns as PIN100722

and then i tried to remove the absolute reference:
{=IFERROR(INDEX(A13:A29, MATCH(0, COUNTIF($A$2:A2, $A$13:$A$29), 0)),"")}

then it copies down all the number in sheet 1

perhaps we should use small? and pick the first number that appears?
 

JDJong

New Member
Joined
May 2, 2018
Messages
39

ADVERTISEMENT

Ah great Thank you!

I think I have figured it out. instead of $A$2:A2 I used $A$3:A3

could you please elaborate a bit why A2 instead of 3 and why match value is 0?
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,202
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
that's to ensure to get a unique list, i.e. to ignore duplicates.
 

JDJong

New Member
Joined
May 2, 2018
Messages
39
Hi Alan,

I was able to apply the formula to get the result that I want.
But I seem to have Problem to filter the result. When i tried to filter it, the result doesn't come up.
How should i reverse it? please advise
 

Watch MrExcel Video

Forum statistics

Threads
1,113,835
Messages
5,544,592
Members
410,621
Latest member
S Oberlander
Top