Macro or Formula to return multiple results in separate row/column

SARABECK

Board Regular
Joined
Jan 5, 2012
Messages
132
Hello,

I have 2 separate system generated reports. Report#1 is my "main report" and report#2 is used for data that is not available in the report#1.

Report #1 - This is a system generated report that has more 10,000+ rows and each row has a unique invoice number but it does not list out the product that it was invoiced for. That information is available in report #2. Therefore, I have incorporated index-match formula to retrieve the name of the products from report#2. The problem I'm now running into is that some of the invoices numbers are split if there are multiple products listed on the invoice, so i added textjoin formula to pull that data in.

For Example

Report#1

It is missing product name and the dollar is a total sum. I have added index-match retrieve the name of the products .

Product Formula - ResultsInvoice#Amount
MissingKiwisabc012345$50
MissingOranges & Appleefg678910$140
MissingStrawberries & Blueberriesxyz111111$70
MissingBanana123111111$10

Report#2
I have added textjoin formula in a helper column to give me a list of the product along with individual dollars amount.

Below Invoice# efg678910 and xyz111111 has multiple products.
ProductInvoice #Amount
Kiwisabc012345
Orangesefg678910$100
Applesefg678910-1$40
Strawberriesxyz111111-2$60
Blueberriesxyz111111-1$10
Banana123111111

I use report #1 to create a pivot table to show product and their cost, however, the multiple products is messing up the pivot table and doesn't seem very useful.

Pivot Table
ProductAmount
Kiwis$50
Oranges & Apple$140
Strawberries & Blueberries$80
Banana$10
On the side of this pivot table I have a text join formula to pull in the amount for each product from report#2, for example, (100 & 40) (60 &10) .

Is there any way i can pull in each product to it own rows/column instead of joined Oranges & Apple 100 & 40 with a macro or formula?

Like -
Orange 100
Apple 40
Strawberries 60
Blueberries 10
Banana 10


Any help is appreciated. thank you in advance.
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Watch MrExcel Video

Forum statistics

Threads
1,127,008
Messages
5,622,140
Members
415,880
Latest member
Bruce0203

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
Top