Formula to transfer multiple rows of entries with reference

[JDJong]

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:
[TABLE="width: 865"]
<tbody>[TR]
[TD]Invoice number[/TD]
[TD]Performer[/TD]
[TD]Descrpition[/TD]
[TD]Tax rate[/TD]
[TD]Invoice amount[/TD]
[TD]Tax Amount[/TD]
[TD]Currency[/TD]
[TD]Performance date[/TD]
[/TR]
[TR]
[TD]PIN100722[/TD]
[TD]Sweptoff[/TD]
[TD]PIN200770[/TD]
[TD="align: right"]0,00[/TD]
[TD="align: right"]18.877,00[/TD]
[TD="align: right"]0,00[/TD]
[TD]USD[/TD]
[TD="align: right"] 03-jan-2018[/TD]
[/TR]
[TR]
[TD]PIN100732[/TD]
[TD]Eastern cleaning[/TD]
[TD]PIN200773[/TD]
[TD="align: right"]23,00[/TD]
[TD="align: right"]24.500,00[/TD]
[TD="align: right"]4.655,00[/TD]
[TD]USD[/TD]
[TD="align: right"] 04-jan-2018[/TD]
[/TR]
[TR]
[TD]PIN100737[/TD]
[TD]DNS[/TD]
[TD]PIN200788[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10.700,00[/TD]
[TD="align: right"]0[/TD]
[TD]USD[/TD]
[TD="align: right"] 12-jan-2018[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col></colgroup>[/TABLE]




Table 1
[TABLE="width: 966"]
<tbody>[TR]
[TD]Invoice number[/TD]
[TD]Company[/TD]
[TD]Serial number[/TD]
[TD]Order number[/TD]
[TD]Tax rate[/TD]
[TD]Invoice amount[/TD]
[TD]Tax Amount[/TD]
[TD]Currency[/TD]
[TD]Performance date[/TD]
[/TR]
[TR]
[TD]PIN100722[/TD]
[TD]Sweptoff[/TD]
[TD]PIN200770[/TD]
[TD]5A1433[/TD]
[TD="align: right"]0,00[/TD]
[TD="align: right"]18.877,00[/TD]
[TD="align: right"]0,00[/TD]
[TD]USD[/TD]
[TD="align: right"] 03-jan-2018[/TD]
[/TR]
[TR]
[TD]PIN100732[/TD]
[TD]Eastern cleaning[/TD]
[TD]PIN200773[/TD]
[TD]5A1885[/TD]
[TD="align: right"]23,00[/TD]
[TD="align: right"]24.000,00[/TD]
[TD="align: right"]4.560,00[/TD]
[TD]USD[/TD]
[TD="align: right"] 03-jan-2018[/TD]
[/TR]
[TR]
[TD]PIN100732[/TD]
[TD]Eastern cleaning[/TD]
[TD]PIN200773[/TD]
[TD]154DN2[/TD]
[TD="align: right"]23,00[/TD]
[TD="align: right"]500,00[/TD]
[TD="align: right"]95,00[/TD]
[TD]USD[/TD]
[TD="align: right"] 03-jan-2018[/TD]
[/TR]
[TR]
[TD]PIN100733[/TD]
[TD]Western cleaning[/TD]
[TD]PIN200779[/TD]
[TD]6A8162[/TD]
[TD="align: right"]23,00[/TD]
[TD="align: right"]650.000,00[/TD]
[TD="align: right"]123.500,00[/TD]
[TD]USD[/TD]
[TD="align: right"] 03-jan-2018[/TD]
[/TR]
[TR]
[TD]PIN100734[/TD]
[TD]Southern cleaning[/TD]
[TD]PIN200755[/TD]
[TD]2A4157[/TD]
[TD="align: right"]0,00[/TD]
[TD="align: right"]16.950,00[/TD]
[TD="align: right"]0,00[/TD]
[TD]USD[/TD]
[TD="align: right"] 08-jan-2018[/TD]
[/TR]
[TR]
[TD]PIN100735[/TD]
[TD]Northern Cleaning[/TD]
[TD]PIN200784[/TD]
[TD]9379M61P03[/TD]
[TD="align: right"]23,00[/TD]
[TD="align: right"]1.292,50[/TD]
[TD="align: right"]245,58[/TD]
[TD]USD[/TD]
[TD="align: right"] 08-jan-2018[/TD]
[/TR]
[TR]
[TD]PIN100736[/TD]
[TD]Oriental[/TD]
[TD]PIN200785[/TD]
[TD]340-051-901-0[/TD]
[TD="align: right"]0,00[/TD]
[TD="align: right"]4.740,00[/TD]
[TD="align: right"]0,00[/TD]
[TD]USD[/TD]
[TD="align: right"] 12-jan-2018[/TD]
[/TR]
[TR]
[TD]PIN100737[/TD]
[TD]DNS[/TD]
[TD]PIN200788[/TD]
[TD]1347M32G08[/TD]
[TD="align: right"]0,00[/TD]
[TD="align: right"]2.850,00[/TD]
[TD="align: right"]0,00[/TD]
[TD]USD[/TD]
[TD="align: right"] 12-jan-2018[/TD]
[/TR]
[TR]
[TD]PIN100737[/TD]
[TD]DNS[/TD]
[TD]PIN200788[/TD]
[TD]3A2704[/TD]
[TD="align: right"]0,00[/TD]
[TD="align: right"]5.000,00[/TD]
[TD="align: right"]0,00[/TD]
[TD]USD[/TD]
[TD="align: right"] 12-jan-2018[/TD]
[/TR]
[TR]
[TD]PIN100737[/TD]
[TD]DNS[/TD]
[TD]PIN200788[/TD]
[TD]6A7906[/TD]
[TD="align: right"]0,00[/TD]
[TD="align: right"]2.850,00[/TD]
[TD="align: right"]0,00[/TD]
[TD]USD[/TD]
[TD="align: right"] 12-jan-2018[/TD]
[/TR]
[TR]
[TD]PIN100738[/TD]
[TD]KEA[/TD]
[TD]PIN200789[/TD]
[TD]340-085-120-0[/TD]
[TD="align: right"]0,00[/TD]
[TD="align: right"]21.000,00[/TD]
[TD="align: right"]0,00[/TD]
[TD]USD[/TD]
[TD="align: right"] 12-jan-2018[/TD]
[/TR]
[TR]
[TD]PIN100739[/TD]
[TD]KLC[/TD]
[TD]PIN200787[/TD]
[TD]1523M71G07[/TD]
[TD="align: right"]0,00[/TD]
[TD="align: right"]2.850,00[/TD]
[TD="align: right"]0,00[/TD]
[TD]USD[/TD]
[TD="align: right"] 12-jan-2018[/TD]
[/TR]
[TR]
[TD]PIN100739[/TD]
[TD]KLC[/TD]
[TD]PIN200787[/TD]
[TD]1851M59P01[/TD]
[TD="align: right"]0,00[/TD]
[TD="align: right"]2.850,00[/TD]
[TD="align: right"]0,00[/TD]
[TD]USD[/TD]
[TD="align: right"] 12-jan-2018[/TD]
[/TR]
[TR]
[TD]PIN100739[/TD]
[TD]KLC[/TD]
[TD]PIN200787[/TD]
[TD]1864M97P01[/TD]
[TD="align: right"]0,00[/TD]
[TD="align: right"]2.850,00[/TD]
[TD="align: right"]0,00[/TD]
[TD]USD[/TD]
[TD="align: right"] 12-jan-2018[/TD]
[/TR]
[TR]
[TD]PIN100740[/TD]
[TD]874EAP[/TD]
[TD]PIN200792[/TD]
[TD]1971M17G01[/TD]
[TD="align: right"]0,00[/TD]
[TD="align: right"]5.000,00[/TD]
[TD="align: right"]0,00[/TD]
[TD]USD[/TD]
[TD="align: right"] 18-jan-2018[/TD]
[/TR]
[TR]
[TD]PIN100741[/TD]
[TD]DLC[/TD]
[TD]PIN200797[/TD]
[TD]340-116-401-0[/TD]
[TD="align: right"]0,00[/TD]
[TD="align: right"]18.000,00[/TD]
[TD="align: right"]0,00[/TD]
[TD]USD[/TD]
[TD="align: right"] 19-jan-2018[/TD]
[/TR]
[TR]
[TD]PIN100742[/TD]
[TD]COPN[/TD]
[TD]PIN200802[/TD]
[TD]9511M24P07[/TD]
[TD="align: right"]0,00[/TD]
[TD="align: right"]7.950,00[/TD]
[TD="align: right"]0,00[/TD]
[TD]USD[/TD]
[TD="align: right"] 22-jan-2018[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col><col></colgroup>[/TABLE]

 

[AlanY]

try

Excel 2013/2016
	A	B	C	D	E	F	G	H	I
1	Results will be like this:
2	Invoice number	Performer	Descrpition	Tax rate	Invoice amount	Tax Amount	Currency	Performance date
3	PIN100722	Sweptoff	PIN200770	0,00	18,877.00	0.00	USD	03-Jan-18
4	PIN100732	Eastern cleaning	PIN200773	23,00	24,500.00	4,655.00	USD	03-Jan-18
5	PIN100733	Western cleaning	PIN200779	23,00	650,000.00	123,500.00	USD	03-Jan-18
6	PIN100734	Southern cleaning	PIN200755	0,00	16,950.00	0.00	USD	08-Jan-18
7	PIN100735	Northern Cleaning	PIN200784	23,00	1,292.50	245.58	USD	08-Jan-18
8	PIN100736	Oriental	PIN200785	0,00	4,740.00	0.00	USD	12-Jan-18
9	PIN100737	DNS	PIN200788	0,00	10,700.00	0.00	USD	12-Jan-18
10
11	Table 1
12	Invoice number	Company	Serial number	Order number	Tax rate	Invoice amount	Tax Amount	Currency	Performance date
13	PIN100722	Sweptoff	PIN200770	5A1433	0,00	18,877.00	0.00	USD	03-Jan-18
14	PIN100732	Eastern cleaning	PIN200773	5A1885	23,00	24,000.00	4,560.00	USD	03-Jan-18
15	PIN100732	Eastern cleaning	PIN200773	154DN2	23,00	500.00	95.00	USD	03-Jan-18
16	PIN100733	Western cleaning	PIN200779	6A8162	23,00	650,000.00	123,500.00	USD	03-Jan-18
17	PIN100734	Southern cleaning	PIN200755	2A4157	0,00	16,950.00	0.00	USD	08-Jan-18
18	PIN100735	Northern Cleaning	PIN200784	9379M61P03	23,00	1,292.50	245.58	USD	08-Jan-18
19	PIN100736	Oriental	PIN200785	340-051-901-0	0,00	4,740.00	0.00	USD	12-Jan-18
20	PIN100737	DNS	PIN200788	1347M32G08	0,00	2,850.00	0.00	USD	12-Jan-18
21	PIN100737	DNS	PIN200788	3A2704	0,00	5,000.00	0.00	USD	12-Jan-18
22	PIN100737	DNS	PIN200788	6A7906	0,00	2,850.00	0.00	USD	12-Jan-18
23	PIN100738	KEA	PIN200789	340-085-120-0	0,00	21,000.00	0.00	USD	12-Jan-18
24	PIN100739	KLC	PIN200787	1523M71G07	0,00	2,850.00	0.00	USD	12-Jan-18
25	PIN100739	KLC	PIN200787	1851M59P01	0,00	2,850.00	0.00	USD	12-Jan-18
26	PIN100739	KLC	PIN200787	1864M97P01	0,00	2,850.00	0.00	USD	12-Jan-18
27	PIN100740	874EAP	PIN200792	1971M17G01	0,00	5,000.00	0.00	USD	18-Jan-18
28	PIN100741	DLC	PIN200797	340-116-401-0	0,00	18,000.00	0.00	USD	19-Jan-18
29	PIN100742	COPN	PIN200802	9511M24P07	0,00	7,950.00	0.00	USD	22-Jan-18
Sheet6

Cell Formulas
Range	Formula
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)
A3	{=IFERROR(INDEX($A$13:$A$29, MATCH(0, COUNTIF($A$2:A2, $A$13:$A$29), 0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[JDJong]

try

Excel 2013/2016

	A	B	C	D	E	F	G	H	I
Results will be like this:
Invoice number	Performer	Descrpition	Tax rate	Invoice amount	Tax Amount	Currency	Performance date
PIN100732	Eastern cleaning	PIN200773	USD
PIN100733	Western cleaning	PIN200779	USD
PIN100734	Southern cleaning	PIN200755	USD
PIN100735	Northern Cleaning	PIN200784	USD
PIN100736	Oriental	PIN200785	USD
PIN100737	DNS	PIN200788	USD
Table 1
Invoice number	Company	Serial number	Order number	Tax rate	Invoice amount	Tax Amount	Currency	Performance date
PIN100722	Sweptoff	PIN200770	5A1433	USD
PIN100732	Eastern cleaning	PIN200773	5A1885	USD
PIN100732	Eastern cleaning	PIN200773	154DN2	USD
PIN100733	Western cleaning	PIN200779	6A8162	USD
PIN100734	Southern cleaning	PIN200755	2A4157	USD
PIN100735	Northern Cleaning	PIN200784	9379M61P03	USD
PIN100736	Oriental	PIN200785	340-051-901-0	USD
PIN100737	DNS	PIN200788	1347M32G08	USD
PIN100737	DNS	PIN200788	3A2704	USD
PIN100737	DNS	PIN200788	6A7906	USD
PIN100738	KEA	PIN200789	340-085-120-0	USD
PIN100739	KLC	PIN200787	1523M71G07	USD
PIN100739	KLC	PIN200787	1851M59P01	USD
PIN100739	KLC	PIN200787	1864M97P01	USD
PIN100740	874EAP	PIN200792	1971M17G01	USD
PIN100741	DLC	PIN200797	340-116-401-0	USD
PIN100742	COPN	PIN200802	9511M24P07	USD

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "]PIN100722[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "]Sweptoff[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "]PIN200770[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]0,00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]18,877.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]0.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "]USD[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]03-Jan-18[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]23,00[/TD]
[TD="align: right"]24,500.00[/TD]
[TD="align: right"]4,655.00[/TD]

[TD="align: right"]03-Jan-18[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]23,00[/TD]
[TD="align: right"]650,000.00[/TD]
[TD="align: right"]123,500.00[/TD]

[TD="align: right"]03-Jan-18[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]0,00[/TD]
[TD="align: right"]16,950.00[/TD]
[TD="align: right"]0.00[/TD]

[TD="align: right"]08-Jan-18[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]23,00[/TD]
[TD="align: right"]1,292.50[/TD]
[TD="align: right"]245.58[/TD]

[TD="align: right"]08-Jan-18[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]0,00[/TD]
[TD="align: right"]4,740.00[/TD]
[TD="align: right"]0.00[/TD]

[TD="align: right"]12-Jan-18[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]0,00[/TD]
[TD="align: right"]10,700.00[/TD]
[TD="align: right"]0.00[/TD]

[TD="align: right"]12-Jan-18[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]0,00[/TD]
[TD="align: right"]18,877.00[/TD]
[TD="align: right"]0.00[/TD]

[TD="align: right"]03-Jan-18[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]23,00[/TD]
[TD="align: right"]24,000.00[/TD]
[TD="align: right"]4,560.00[/TD]

[TD="align: right"]03-Jan-18[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]23,00[/TD]
[TD="align: right"]500.00[/TD]
[TD="align: right"]95.00[/TD]

[TD="align: right"]03-Jan-18[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]23,00[/TD]
[TD="align: right"]650,000.00[/TD]
[TD="align: right"]123,500.00[/TD]

[TD="align: right"]03-Jan-18[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]0,00[/TD]
[TD="align: right"]16,950.00[/TD]
[TD="align: right"]0.00[/TD]

[TD="align: right"]08-Jan-18[/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]23,00[/TD]
[TD="align: right"]1,292.50[/TD]
[TD="align: right"]245.58[/TD]

[TD="align: right"]08-Jan-18[/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]0,00[/TD]
[TD="align: right"]4,740.00[/TD]
[TD="align: right"]0.00[/TD]

[TD="align: right"]12-Jan-18[/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]0,00[/TD]
[TD="align: right"]2,850.00[/TD]
[TD="align: right"]0.00[/TD]

[TD="align: right"]12-Jan-18[/TD]

[TD="align: center"]21[/TD]

[TD="align: right"]0,00[/TD]
[TD="align: right"]5,000.00[/TD]
[TD="align: right"]0.00[/TD]

[TD="align: right"]12-Jan-18[/TD]

[TD="align: center"]22[/TD]

[TD="align: right"]0,00[/TD]
[TD="align: right"]2,850.00[/TD]
[TD="align: right"]0.00[/TD]

[TD="align: right"]12-Jan-18[/TD]

[TD="align: center"]23[/TD]

[TD="align: right"]0,00[/TD]
[TD="align: right"]21,000.00[/TD]
[TD="align: right"]0.00[/TD]

[TD="align: right"]12-Jan-18[/TD]

[TD="align: center"]24[/TD]

[TD="align: right"]0,00[/TD]
[TD="align: right"]2,850.00[/TD]
[TD="align: right"]0.00[/TD]

[TD="align: right"]12-Jan-18[/TD]

[TD="align: center"]25[/TD]

[TD="align: right"]0,00[/TD]
[TD="align: right"]2,850.00[/TD]
[TD="align: right"]0.00[/TD]

[TD="align: right"]12-Jan-18[/TD]

[TD="align: center"]26[/TD]

[TD="align: right"]0,00[/TD]
[TD="align: right"]2,850.00[/TD]
[TD="align: right"]0.00[/TD]

[TD="align: right"]12-Jan-18[/TD]

[TD="align: center"]27[/TD]

[TD="align: right"]0,00[/TD]
[TD="align: right"]5,000.00[/TD]
[TD="align: right"]0.00[/TD]

[TD="align: right"]18-Jan-18[/TD]

[TD="align: center"]28[/TD]

[TD="align: right"]0,00[/TD]
[TD="align: right"]18,000.00[/TD]
[TD="align: right"]0.00[/TD]

[TD="align: right"]19-Jan-18[/TD]

[TD="align: center"]29[/TD]

[TD="align: right"]0,00[/TD]
[TD="align: right"]7,950.00[/TD]
[TD="align: right"]0.00[/TD]

[TD="align: right"]22-Jan-18[/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3[/TH]
[TD="align: left"]=LOOKUP(A3,$A$13:$A$29,$B$13:$B$29)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C3[/TH]
[TD="align: left"]=LOOKUP(A3,$A$13:$A$29,$C$13:$C$29)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D3[/TH]
[TD="align: left"]=LOOKUP(A3,$A$13:$A$29,$E$13:$E$29)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E3[/TH]
[TD="align: left"]=SUMIF($A$13:$A$29,A3,$F$13:$F$29)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3[/TH]
[TD="align: left"]=SUMIF($A$13:$A$29,A3,$G$13:$G$29)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G3[/TH]
[TD="align: left"]=LOOKUP(A3,$A$13:$A$29,$H$13:$H$29)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H3[/TH]
[TD="align: left"]=LOOKUP(A3,$A$13:$A$29,$I$13:$I$29)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A3[/TH]
[TD="align: left"]{=IFERROR(INDEX($A$13:$A$29, MATCH(0, COUNTIF($A$2:A2, $A$13:$A$29), 0)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

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]

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

 

[JDJong]

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?

 

[AlanY]

not sure what happened, have a look of the one I'd prepared earlier

https://drive.google.com/file/d/1Y2XP4gHNSCZlW2ZZWEFFdPBOS8YztjyI/view?usp=sharing

 

[JDJong]

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]

that's to ensure to get a unique list, i.e. to ignore duplicates.

 

[JDJong]

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