ReBuild a concatenated cell based on format of second cell - VBA solution needed

[jfarc]

I'm pulling Sale & Cost data from a mainframe into Excel, which has multi-value data stored in a single variable(cell), one field for multiple Costs & one field for multiple Sales from a single Invoice.

The problem is the Sale field from the mainframe fills in '0.00' when there is no pricing info for a particular line on the Invoice, but the Cost field doesn't do this, it only reports Cost amounts that are greater than zero. I want to rebuild the Cost field so it has the missing '0.00' for each corresponding Sale of '0.00'.

The example fields below will make this more clear on what is happening and what is needed. The example has 3 rows of data. Each row can have a variable number of separate dollar fields each separated by a space.

This data is in Column-K Sale info. Each line below is a single cell(row) that contains multiple Sale $figures:

154.63 0.00 0.00 0.00 0.00 140.00 0.00 0.00
0.00 0.00 25.33 0.00 0.00 20.16 15.32
0.00 4.24 0.00 2.48 146.68

The data is in Column J Cost info. This is first 3 rows where only the Cost info that is greater than zero is displayed:

101.41 96.00
19.48 12.50 9.50
3.03 1.77 95.35

So in the first row from Col-J 101.41 corresponds to the Col-K 154.63 and Col-J 96.00, the 2nd $figure corresponds to Col-K 140.00, which is the 6th $figure. This row has a total of 8 Sale figures, but only 2 Cost figures.

In the second row from Col-J 19.48, the 1st $figure corresponds to the Col-K 25.33, which is the 3rd $figure and so on.... This row has a total of 7 Sale figures.

In the end, what I need is to rebuild each cell in Col-J so that the missing '0.00' amounts are filled in as follows so that each and every dollar amount in Col-J fully corresponds one-for-one to each dollar amount in Col-K and remain in the same original format separated by spaces:

101.41 0.00 0.00 0.00 0.00 96.00 0.00 0.00
0.00 0.00 19.48 0.00 0.00 12.50 9.50
0.00 3.03 0.00 1.77 95.35

Once I rebuild Col-J, I already have working code to process the file from that point.

 

[jfarc]

Ok, I came up with the following inside my entire file processing loop to resolve this issue and it's working nicely:

        jNew = ""
        jCount = 0
        j1Split = Split(Range("J" & x), " ")
        k1Split = Split(Range("K" & x), " ")
        For iCount = LBound(k1Split) To UBound(k1Split)
            If k1Split(iCount) <> 0 Then
                jNew = jNew & j1Split(jCount) & " "
                jCount = jCount + 1
            Else
                jNew = jNew & "0.00" & " "
            End If
        Next iCount
        Range("J" & x) = Left(jNew, Len(jNew) - 1)