Convert comma separated string into column, WITH adjacent row's data

Hello.

I have four columns of data. They consist of Customer Name, Product, Amount and the tough part -- Dates (in which there can be multiple dates, values).

I need almost the same result, except I just need one date in each cell. If there are two dates, I need the duplicate the Customer Name & Customer Product on another row and have the second/third/fourth dates, etc.

See below:

A1	B1	C1	D1
Cust Name	Cust Offering	Amount	Date
Cust A	Product B	10,000	Mar-17, Apr-18
Cust B	Product A	0	N/A
Cust C	Product G	25,000	Jan-17, Oct-18, Nov-18, Feb-19
Cust D	Product A	15,000	Dec-18, Jan-20

The result from the above should be almost the same format as above, except every instance of multiple dates duplicates the entry.

RESULT:

Cust A | Product B | 10,000 | Mar-17
Cust A | Product B | 10,000 | Apr-18
Cust C | Product G | 25,000 | Jan-17
Cust C | Product G | 25,000 | Oct-18
Cust C | Product G | 25,000 | Nov-18
Cust C | Product G | 25,000 | Feb-19
Cust D | Product A | 15,000 | Dec-18
Cust D | Product A | 15,000 | Jan-20


Can anyone assist? Thank you!

See attached file.

Let me know if you need any explanations.

Hope this helps.

Pete

Originally Posted by Pete_UK

See attached file.

Let me know if you need any explanations.

Hope this helps.

Pete

Pete you're a genius. Care to explain what's happening here? I see we're counting commas essentially, how does the piece at the bottom work? Having a tough time putting that together.

Thank you!

Sorry, I do normally post with explanations, but I was about to go off-line and watch a bit of TV when I posted earlier, so I thought you would like the solution first, with explanations to follow.

As you have deduced, column E is used to count the number of commas in each row, with 1 added on to give the number of date items for each row. Column F just gives a cumulative count of the number of date items from column D (i.e. the number of expanded records we will have in the second table). The formulae in columns A to C of the lower table are virtually the same, with this in A11:

=IFERROR(INDEX(A$2:A$5,MATCH(ROWS($1:1)-1,$F$1:$F$5)),"")

It is important to realise here that the ROWS($1:1) term (which initially returns 1) will become ROWS($1:2), then ROWS($1:3) on subsequent rows when the formula is copied down. Thus it will return 2, then 3, then 4 etc. on subsequent rows, and 1 is subtracted from this to give a sequence of 0, 1, 2, 3, etc. This is embodied within the MATCH function, and this looks to see where the sequential number occurs within column F, and then the INDEX part of the formula returns that element from column A. When the formula is copied across to B11 and C11, only the INDEX(A$2:A$5 part changes, so that data is brought from columns B and C respectively.

The formula in D11 is a bit more complicated:

=IFERROR(MID(", "&INDEX(D$2:D$5,MATCH(ROWS($1:1)-1,$F$1:$F$5)),FIND(",",", "&INDEX(D$2:D$5,MATCH(ROWS($1:1)-1,$F$1:$F$5)),(COUNTIF(A$11:A11,A11)-1)*8+1)+2,6),"")

You can still see the same basic INDEX/MATCH formula as above (shown in red), but in this term we only want to pick out part of the item from column D, so it is embedded within a MID function to enable us to do this. The syntax for MID is:

MID(text, start_character, number_of_characters)

and the text is the result of the INDEX/MATCH functions but with a comma-space added on to the beginning. The FIND function allows us to look for the start of the sub-string that we want to extract, so we want to look for a comma within that text (hence why the ", " is added on to the beginning). Initially, we want to extract the first substring, but subsequently we will want the second, then the third etc., and the FIND function allows us to have a start_num which is where the COUNTIF term (coloured blue above) comes in, as it counts the number of items in column A so far. As each sub-string is 8 characters wide including the comma-space, we subtract 1 from the COUNTIF term and multiply this by 8, and then 2 is added on because our actual start_character is 2 characters in. The number of characters we want for each sub-string is 6.

All the formulae have an IFERROR( ... ,"") term around them to return blanks if we have run out of records to process - in the file above I had copied the formulae down to row 20, although we only have enough data for up to row 18.

Anyway, hope this helps.

Pete

Originally Posted by Pete_UK

Sorry, I do normally post with explanations, but I was about to go off-line and watch a bit of TV when I posted earlier, so I thought you would like the solution first, with explanations to follow.

As you have deduced, column E is used to count the number of commas in each row, with 1 added on to give the number of date items for each row. Column F just gives a cumulative count of the number of date items from column D (i.e. the number of expanded records we will have in the second table). The formulae in columns A to C of the lower table are virtually the same, with this in A11:

=IFERROR(INDEX(A$2:A$5,MATCH(ROWS($1:1)-1,$F$1:$F$5)),"")

It is important to realise here that the ROWS($1:1) term (which initially returns 1) will become ROWS($1:2), then ROWS($1:3) on subsequent rows when the formula is copied down. Thus it will return 2, then 3, then 4 etc. on subsequent rows, and 1 is subtracted from this to give a sequence of 0, 1, 2, 3, etc. This is embodied within the MATCH function, and this looks to see where the sequential number occurs within column F, and then the INDEX part of the formula returns that element from column A. When the formula is copied across to B11 and C11, only the INDEX(A$2:A$5 part changes, so that data is brought from columns B and C respectively.

The formula in D11 is a bit more complicated:

=IFERROR(MID(", "&INDEX(D$2:D$5,MATCH(ROWS($1:1)-1,$F$1:$F$5)),FIND(",",", "&INDEX(D$2:D$5,MATCH(ROWS($1:1)-1,$F$1:$F$5)),(COUNTIF(A$11:A11,A11)-1)*8+1)+2,6),"")

You can still see the same basic INDEX/MATCH formula as above (shown in red), but in this term we only want to pick out part of the item from column D, so it is embedded within a MID function to enable us to do this. The syntax for MID is:

MID(text, start_character, number_of_characters)

and the text is the result of the INDEX/MATCH functions but with a comma-space added on to the beginning. The FIND function allows us to look for the start of the sub-string that we want to extract, so we want to look for a comma within that text (hence why the ", " is added on to the beginning). Initially, we want to extract the first substring, but subsequently we will want the second, then the third etc., and the FIND function allows us to have a start_num which is where the COUNTIF term (coloured blue above) comes in, as it counts the number of items in column A so far. As each sub-string is 8 characters wide including the comma-space, we subtract 1 from the COUNTIF term and multiply this by 8, and then 2 is added on because our actual start_character is 2 characters in. The number of characters we want for each sub-string is 6.

All the formulae have an IFERROR( ... ,"") term around them to return blanks if we have run out of records to process - in the file above I had copied the formulae down to row 20, although we only have enough data for up to row 18.

Anyway, hope this helps.

Pete

Incredible. This helped so much. Thank you for taking the time to write it out and explain.

You're welcome, and thanks for the rep yesterday.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Pete