Splitting Row by values into multiple rows

**subvtech** · 01-27-2023, 11:15 AM

Cross-posted at https://www.mrexcel.com/board/thread...-rows.1228217/

Hello,

I'm trying to split these rows into multiple rows, maintaining the row headings. The data looks like this:

Please Login or Register  to view this content.

I'm trying to group by values and splitting them into multiple rows, keeping the header:

Please Login or Register  to view this content.

I've attached a sample sheet.

I have tried using MakeArray, but I'm unable to grab that keep that row header... just the values after.

SVT

**vichopalacios** · 01-27-2023, 12:32 PM

Hi @subvtech
I can’t foresee how to do it using only formula
Have you thought about using VBA?
that would be very simple

**Limor_OP** · 01-27-2023, 12:59 PM

Hi,
can you please attach the formula you have crafted with MAKEARRAY?

thanks.

**subvtech** · 01-27-2023, 01:22 PM

Originally Posted by vichopalacios

Hi subvtech
I can’t foresee how to do it using only formula
Have you thought about using VBA?
that would be very simple

No, I haven't thought about VBA.

**subvtech** · 01-27-2023, 01:31 PM

Originally Posted by Limor_OP

Hi,
can you please attach the formula you have crafted with MAKEARRAY?

thanks.

Attached is as far as I made it. It only gathers values from a specific column. Took me for ever get that far. Find pieces here and there on the webs.

**Limor_OP** · 01-27-2023, 01:45 PM

I though you were looking for horizontal solution that runs for each line , and not for each column....

**subvtech** · 01-27-2023, 02:27 PM

Originally Posted by Limor_OP

I though you were looking for horizontal solution that runs for each line , and not for each column....

You are correct. I was working with a column initially and tried to apply it to horizontal data. This is as far as I had gotten. I am in fact looking to split rows of data into multiple rows grouped by numbers. I can only group the numbers in a column

**Limor_OP** · 01-27-2023, 03:27 PM

Hi,
this is the best I could find for now, with 3 different formulae for A, B and C:

A9 = GROUP-1;
A10 and down:

Please Login or Register  to view this content.

B9 and down:

Please Login or Register  to view this content.

C9 and drag across:

=IF(COUNTIF($B9:B9,$B9)<SUM(--(INDEX($B$2:$F$5,MATCH($A9,$A$2:$A$5,0),COLUMN($A$1:$E$1))=$B9)),$B9,"")

**6StringJazzer** · 01-27-2023, 03:58 PM

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/

I added the link to post #1 since you are new.

**subvtech** · 01-27-2023, 04:02 PM

Originally Posted by Limor_OP

Hi,
this is the best I could find for now, with 3 different formulae for A, B and C:

A9 = GROUP-1;
A10 and down:

Please Login or Register  to view this content.

B9 and down:

Please Login or Register  to view this content.

C9 and drag across:

=IF(COUNTIF($B9:B9,$B9)<SUM(--(INDEX($B$2:$F$5,MATCH($A9,$A$2:$A$5,0),COLUMN($A$1:$E$1))=$B9)),$B9,"")

Interesting. I am trying it out now.

**subvtech** · 01-27-2023, 04:04 PM

Rule Learned.

Thank you

**subvtech** · 01-27-2023, 05:35 PM

Originally Posted by Limor_OP

Hi,
this is the best I could find for now, with 3 different formulae for A, B and C:

A9 = GROUP-1;
A10 and down:

Please Login or Register  to view this content.

B9 and down:

Please Login or Register  to view this content.

C9 and drag across:

=IF(COUNTIF($B9:B9,$B9)<SUM(--(INDEX($B$2:$F$5,MATCH($A9,$A$2:$A$5,0),COLUMN($A$1:$E$1))=$B9)),$B9,"")

This is great.. However, it doesn't like letters and for some reason there appears to be a line of #REF! between the breaks. I really don't mind as long as it is working. I've attached my results.
This give me hope that there is light at the end of the road

**Limor_OP** · 01-27-2023, 05:40 PM

Hi,
here is your file with my formulae applied:

A16:

Please Login or Register  to view this content.

B15:

Please Login or Register  to view this content.

C15 and across:

=LET(c,INDEX($B$2:$M$11,MATCH($A15,$A$2:$A$11,0),COLUMN($A$1:$L$1)),IF(COUNTIF($B15:B15,$B15)<SUM(--(c=$B15)*--(c<>"")),$B15,""))

**Limor_OP** · 01-27-2023, 05:43 PM

Originally Posted by subvtech

This is great.. However, it doesn't like letters and for some reason there appears to be a line of #REF! ...........

Hey
did you check my latest post?
I have applied the formulae on your sample, and made some changes since you have "0" also in your real data which I didn't take into account in the initial sample, and since blank cells are also counted as "o"s so I had to adjust that....

Let me know your thoughts.

**Limor_OP** · 01-27-2023, 06:38 PM

Hi,
here is the new version (next time please post a sample similar to your real data as the formulae might differ if you add for example alphanumeric characters)

In addition - you had a few cells that just looked empty but they had a hidden character filled in, which added another empty line to the results, so I deleted the content of these cells.

**subvtech** · 01-28-2023, 12:51 AM

Originally Posted by Limor_OP

Hi,
here is the new version (next time please post a sample similar to your real data as the formulae might differ if you add for example alphanumeric characters)

In addition - you had a few cells that just looked empty but they had a hidden character filled in, which added another empty line to the results, so I deleted the content of these cells.

This is working as intended. Much appreciated.

That first row, COLUMN($A$1:$T$1), is supposed to be one cell sorted then the range below it correct? Looks that way.

**Limor_OP** · 01-28-2023, 05:15 AM

Hi
you're welcome.

the first row in the formula is part of the INDEX function.
It finds the range for each header accordingly (before narrowed down by UNIQUE)
you can run this segment independently and see what you will get...
Thanks for the feedback : )

=IFERROR(INDEX(UNIQUE(TOCOL(FILTER(UNIQUE(INDEX($B$2:$U$7,MATCH(A9,$A$2:$A$7,0),COLUMN($A$1:$T$1)))),UNIQUE((INDEX($B$2:$U$7,MATCH(A9,$A$2:$A$7,0),COLUMN($A$1:$T$1))))<>""))),COUNTIF($A$9:A9,A9)),"")

**6StringJazzer** · 01-28-2023, 11:20 AM

Originally Posted by subvtech

This is working as intended. Much appreciated.

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

If a member helped you solve your problem, consider adding to their reputation by clicking addreputationiconsmall.jpg below their name.

**subvtech** · 02-02-2023, 09:33 PM

Originally Posted by 6StringJazzer

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

If a member helped you solve your problem, consider adding to their reputation by clicking Attachment 815395 below their name.

Trying to mark thread as solved.. can't find the button

**6StringJazzer** · 02-02-2023, 10:45 PM

Do you see Thread Tools above your first post?

solved.jpg

Splitting Row by values into multiple rows

LinkBack

Thread Tools

Rate This Thread

Display

Splitting Row by values into multiple rows

Re: Splitting Row by values into multiple rows

Re: Splitting Row by values into multiple rows

Re: Splitting Row by values into multiple rows

Re: Splitting Row by values into multiple rows

Re: Splitting Row by values into multiple rows

Re: Splitting Row by values into multiple rows

Re: Splitting Row by values into multiple rows

Re: Splitting Row by values into multiple rows

Re: Splitting Row by values into multiple rows

Re: Splitting Row by values into multiple rows

Re: Splitting Row by values into multiple rows

Re: Splitting Row by values into multiple rows

Re: Splitting Row by values into multiple rows

Re: Splitting Row by values into multiple rows

Re: Splitting Row by values into multiple rows

Re: Splitting Row by values into multiple rows

Re: Splitting Row by values into multiple rows

Re: Splitting Row by values into multiple rows

Re: Splitting Row by values into multiple rows

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Splitting multiple cell values into rows (Unpivot)

Splitting multiple lines of data within a single cell into multiple rows?

[SOLVED] Splitting Cell in multiple rows

Splitting Cells with multiple values into separate rows

Splitting rows with multiple columns into seperate rows for time points.

[SOLVED] Splitting 1 row into multiple rows

splitting multiple rows of data in one cell into multiple cells

Bookmarks

Bookmarks

Posting Permissions