Formula to transfer multiple rows of entries with reference

J

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>
 

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
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
Sheet6
Cell Formulas
RangeFormula
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.
 
Upvote 0
AlanY said:
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>
Click to expand...


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 ?
 
Upvote 0
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:
Upvote 0
AlanY said:
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
Click to expand...

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?
 
Upvote 0
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?
 
Upvote 0
that's to ensure to get a unique list, i.e. to ignore duplicates.
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
formula to extract multiple rows based on referenec.
Replies
0
Views
416
JDJong
J
A
  • Question
Data summary - transfer certain columns of rows 2 criteria (VBA or Formula)
Replies
1
Views
608
Aberdham
A

Forum statistics

Threads
1,214,430
Messages
6,119,453
Members
448,898
Latest member
drewmorgan128

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