Organize data to create pivot table when multiple values in one cell

You could do it using a few formulae, but the effectiveness of this approach will depend on what you mean by "... a huge set of data …"

Perhaps you could attach a sample Excel workbook, and give an indication of how much data you have in your real file.

Pete

Hi Pete_UK,

Thank you for taking a look. So, to give you an idea, the set of data has 35 columns and can easily have thousands of lines (I have one that has more than 200 000 lines).

I am attaching a sample of how it looks like (only with two lines so you can have an idea) but the real file may then have much more.

And as you can see, only the "Conversation tags" column is the one that poses a problem for me.

Hi JeteMc,

Thanks that looks to be going into the right direction.

In my sample data there are only two lines. But what if I have more than than two lines? Let's say I have 42090 lines. Would in that case the two formulas be like:

=IFERROR(INDEX(A$2:A$42090,AGGREGATE(15,6,(ROW($A$2:$A$42090)-ROW($A$1))/(ROWS($A$1:$A1)<=$AK$2:$AK$42090),1)),"")

and

=TRIM(MID(SUBSTITUTE(INDEX(J$2:J$42090,AGGREGATE(15,6,(ROW($A$2:$A$42090)-ROW($A$1))/(ROWS($A$1:$A1)<=$AK$2:$AK$42090),1)),",",REPT(" ",LEN(INDEX(J$2:J$42090,AGGREGATE(15,6,(ROW($A$2:$A$42090)-ROW($A$1))/(ROWS($A$1:$A1)<=$AK$2:$AK$42090),1))))),(COUNTIFS(AL$2:AL2,AL2)-1)*LEN(INDEX(J$2:J$42090,AGGREGATE(15,6,(ROW($A$2:$A$42090)-ROW($A$1))/(ROWS($A$1:$A1)<=$AK$2:$AK$42090),1)))+1,LEN(INDEX(J$2:J$3,AGGREGATE(15,6,(ROW($A$2:$A$42090)-ROW($A$1))/(ROWS($A$1:$A1)<=$AK$2:$AK$42090),1)))))

Would that be correct?

I would have thought that might become a bit unwieldly if you have nearly 50,000 rows (and especially for the file that has more than 200,000 rows, as mentioned in Post #3).

Let us know how you get on with applying it to such large files, as I have a solution which might be less demanding on resources.

Pete

JeteMC, thank you!

And just to be sure, when you write that you should have prefixed the second formula with =IF(AL2="","", that means that the beginning of the formula would be: "=IF(AL2="","",TRIM(MID(SUBSTITUTE(INDEX(J$..." => is that correct?

I think this is just great for small files!

However, Pete_UK: you are right, my computer is freezing for larger files So, if you have a solution which might be less demanding on resources I would really appreciate if you could share it!

I have attached my version. I have taken the same initial approach as JeteMc and used two helper columns (AJ and AK), with these formulae in the cells stated:

AJ2: =LEN(J2&",")-LEN(SUBSTITUTE(J2&",",",",""))
AK2: =SUM(AK1,AJ2)

Copy these down to the bottom of your data. In addition, I have put zero in cell AK1, which is important for the other formulae.

I've kept column AL blank to separate the two tables, and then basically used INDEX and MATCH. Instead of having the MATCH component in every formula, I have set this up once in column AM, with this formula in AM2:

=IF(ROWS($1:1)>MAX($AK:$AK),"",MATCH(ROWS($1:1)-1,$AK:$AK)+1)

and this should be copied down until you start to get blanks. This means that I can use this formula in AN2:

=IF($AM2="","",INDEX(A:A,$AM2))

which is the start of the data that you want, and this can basically be copied all the way across to cell BV2, although AW2 will need to be changed. However, I noticed that some of your data fields have blank entries, so to show these as blanks I have used this variation to the formula (e.g. in AO2):

=IF($AM2="","",INDEX(B:B,$AM2)&"")

I have copied this across to all those columns where the entry is a text value, so that blanks show as blanks without having a construct like (IF(formula="","",formula). Do not do this with numeric or date values, however.

AW2 needs to have its own special formula to separate out the comma-separated values:

=IF($AM2="","",TRIM(MID(SUBSTITUTE(INDEX(J:J,$AM2),",",REPT(" ",100)), (ROWS($1:1)-INDEX(AK:AK,MATCH(ROWS($1:1)-1,AK:AK))-1)*100+1,100)))

The red sections are basically the same formula as in the other cells, and the other parts split out the value to take the first word, then the second on the next line, and so on.

All the formulae from AN2 to BV2 can then be copied down as far as the formula in column AM has been copied, although you might find it better in a large file to copy down each column in turn, by double-clicking the fill handle (the small black square in the bottom right corner of the cursor).So, having copied down AM2 as far as necessary, select AN2 the double-click, then AO2, double-click, and so on.

I would expect this approach to be less sluggish than using AGGREGATE for large files, but as I said in Post #2 it is not necessarily a good option to use formulae with such large files.

Hope this helps.

Pete

Hi JeteMc and Pete_UK,

This is just brilliant! I didn't think this could be solved and you made it!

Thank you soooo much

That's good to hear, and thanks for the rep.

Out of interest, was there any significant difference between the times taken in the two approaches?

Have you tried it on your file with +200,000 rows?

Pete

Hi Pete,

Your approach required indeed less time but I did not try it on the big file since it's still too heavy a set of data.

I think I'll just have to adapt how I do things but having both your approaches gives me more flexibility and above all: a solution - which I didn't have at all

What you might want to try with the big file is to copy the formulae down a few rows at a time and then fix the values, then copy down further and fix those values etc. So if you copy down to row 5001, fix the values in rows 2 to 5000, then copy row 5001 down to 10,001 and fix the values in 5001 to 10,000, and so on.

Anyway, you seem happy with what you've got, so I'll leave it at that.

Pete

I've been following this thread as I need to do something similar, break out a cell with multiple values into individual rows. I've downloaded the spreadsheet and applied the formulas which seem to work for the first couple of values but when it looks at several values in the cell it begins to add blank rows and break up the values that I am trying to sort out. I've attached my spreadsheet here. I've inserted values into column J and can see them broken out into column AW. However you can see in the cells that are highlighted red of column AW they are either blank or they separate the values from column J such as in cell AW21 and AW22. What am I missing here?