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 .
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.
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
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.
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 - Results | Invoice# | Amount |
|---|---|---|---|
| Missing | Kiwis | abc012345 | $50 |
| Missing | Oranges & Apple | efg678910 | $140 |
| Missing | Strawberries & Blueberries | xyz111111 | $70 |
| Missing | Banana | 123111111 | $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.
| Product | Invoice # | Amount |
|---|---|---|
| Kiwis | abc012345 | |
| Oranges | efg678910 | $100 |
| Apples | efg678910-1 | $40 |
| Strawberries | xyz111111-2 | $60 |
| Blueberries | xyz111111-1 | $10 |
| Banana | 123111111 |
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
| Product | Amount |
|---|---|
| Kiwis | $50 |
| Oranges & Apple | $140 |
| Strawberries & Blueberries | $80 |
| Banana | $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.
