Separating values

[macok]

Hello All,
In the attached file I have values on Column A of the Raw Tab. For each value on column A there are a number of corresponding values in columns O and P. I need to create a summary where values in columns O and P are separated and it looks like the example in Summary Tab.

Any assistance would be greatly appreciated.

[Bernie Deitrick]

On your Summary sheet - change the '100's in the formulas to something higher than your actual last row

In O2:
=TEXTSPLIT(TEXTJOIN(",",,Raw!O2:O100),,",")

In P2:
=TEXTSPLIT(TEXTJOIN(",",,Raw!P2:P100),,",")

In A2:
=IFERROR(INDEX(Raw!A:A,MATCH($O$2#,LEFT(Raw!$O$1:$O$100,9),FALSE)),"")

In N2:
=IFERROR(INDEX(Raw!N:N,MATCH($O$2#,LEFT(Raw!$O$1:$O$100,9),FALSE)),"")

[TMS]

Maybe this:

=LET(data,TRIMRANGE(Raw!A2:P1000),a,INDEX(data,,1),n,INDEX(data,,14),o,INDEX(data,,15),p,INDEX(data,,16),HSTACK(WRAPROWS(TEXTSPLIT(TEXTJOIN("|",,MAP(a,n,o,LAMBDA(a,n,o,TEXTJOIN("|",,a&"|"&n&"|"&TEXTSPLIT(o,","))))),"|"),3),TEXTSPLIT(TEXTJOIN(",",,p),,","))) 

[macok]

Thanks TMS. Are you able to attach a copy of the outcome? Im not sure I understand exactly how to apply.

[macok]

Hey Bernie, are you able to attach the outcome?
I do not understand exactly what to apply where.
Thank you!

[TMS]

Sure, this updated file has both approaches. I've adjusted the order of Bernie's formulae.

[macok]

Thank you, TMS, very much appreciated.
One more question: The data I need to run these formulae runs into the thousands of rows. Whan I try to run changing the numbers it gives a CALC error. Looking at the formula error it says text is too long. Do you have any suggestions?

[windknife]

Try this,

Please Login or Register  to view this content.

[macok]

Windknife. Thank you so much for your input, and thank you for the late reply. I applied it but it's giving an error message-Please see attached. Is there any way you can apply in the attached and post the outcome? I only need columns A,N,O,P.

Thanks!

[macok]

Meant sorry for the late reply

[sanjeev1976]

Updated file using formula from Windknife :

[macok]

Thank you very much for the quick response. Im trying to apply this formula by either coping what you entered in the outcome, rewriting it allover again from the beginning but just seems to be too finicky, and gives the error "#NAME?
". I even tried to copy and paste values from the file you sent to just below the row 5, and once I do that the formula disappeared and the error #NAME? shows up. Is there any advice? Anything I am missing? I am very sorry for all these may be non sense questions, but at the same time very appreciative of your time!

[TMS]

That normally means that one or more of the functions is not available in your version of Excel, or one of the variables used has not been defined, or a misspelling, etc.

Given the formula works for me, and others, it's not the variables. So, that leaves either a user input error, or the version of Excel is not fully updated.

I'd suggest checking/forcing an update and trying again.

[macok]

Thanks TMS!
Could there possibly be another, simpler formula?

[windknife]

You don't have 'raw' sheet. If you use formula in the same sheet, try this

Please Login or Register  to view this content.

[macok]

Hi Windknife. Thank you kindly for the update. I really appreciate it.
Im not sure why I cant make this formula work. My version of excel is 365 and is all updated. I have attached the file in here, I would greatly appreciate it if you could apply the formula one more time. Please note that there are rows where columns "O" and "P" are empty and I need to keep them in there. Also, Please keep in mind that I might need to add a couple more thousands rowsThanks!

[windknife]

One guess, try this in Q6.

Please Login or Register  to view this content.