Separating CSV data into rows

[rshenk3]

Hello you wonderful people,

I am trying to create an expense calculator based on order data.  When I enter the Invoice # in the box at top, data populates into row 7 as csv's.  I need help with a VBA code that separates the csv values into rows so I can then calculate the expenses associated which each line item.  I have been working on a code but I can only get it to show the first value in the next row, not all the values in new rows.  I have screenshots of the calculator and also the code I have so far.  I would be happy to provide any additional information needed.  Thanks so much for your help!

[#2 · Accepted solution]

Hi @rshenk3 

If I understand your needs correctly, and you have an Excel version with FILTERXML and XLOOKUP you can do it without macros.

The main problem seems to be separating the csv data, and that can be done using FILTERXML.

=FILTERXML("<c><r>"&SUBSTITUTE(H7;",";"</r><r>")&"</r></c>";"//r")

Substitute the commas to make it parsable as XML;

<c><r>1</r><r>2</r><r>3</r><r>4</r></c>

That formula in H10 results in one column with multiple rows.

J10 shows the SUMPRODUCT, or simply =SUM(H10#*I10#) assuming Excel 365.

In the attaced file, you also have an example of looking up the wanted invoice row. cell P10

=LET(
   qtyCell;     XLOOKUP(H3;B:B;H:H);
   priceCell;   XLOOKUP(H3;B:B;I:I);
   qty;         FILTERXML("<c><r>"&SUBSTITUTE(qtyCell;",";"</r><r>")&"</r></c>";"//r");
   price;       FILTERXML("<c><r>"&SUBSTITUTE(priceCell;",";"</r><r>")&"</r></c>";"//r");
   output;      SUM(qty*price);
   output
)

[#3]

Thank you so much for your help!