How can I get the column values combination in excel

Question

0.00/5 (No votes)

See more:

Excel-2016

Hello,
I have table with matrix like this:

ID	Sub1 Sub2 Sub3 Sub4
A	Y	 Y	  Y	
B		 Y	  Y	   Y
C	Y	 Y	  Y
D	Y	 Y	  Y	   Y

I want output like this:

A Sub1 Y
A Sub2 Y
A Sub3 Y
A Sub4
B Sub1
B Sub2 Y
B Sub3 Y
B Sub4 Y
C Sub1 Y
C Sub2 Y
C Sub3 Y
C Sub4
D Sub1 Y
D Sub2 Y
D Sub3 Y
D Sub4 Y

Basically, I want the combination of each ID with each sub and I want to get the value 'Y' wherever is applicable.

What I have tried:

I have tried to do a Pivot table, group table, column to text, xlookup but nothing is working. Can anyone please help me with this?

Posted 2-Jun-22 17:54pm

webmail123

Updated 6-Jun-22 10:18am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Patrice T · Answer 1 · 2022-06-02T18:53:00

Quote:
I have tried to do a Pivot table, group table, column to text, xlookup but nothing is working.

You want to unpivot the table
Unpivot Excel Data - Excel University[^]
Pivot is converting a flat list (tabular data) to crosstab.
Unpivot is converting a crosstab to flat list.

If you can't use this solution, you still can do the job manually.
First I would do a special copy of the crosstab with transpose option.
Then copy/paste pieces as needed.
Transpose (rotate) data from rows to columns or vice versa[^]
Transpose Data in Excel (In Easy Steps)[^]

matblue25 · Answer 2 · 2022-06-06T10:18:00

If you want to do it with a formula, there are a lot of ways to do it but here's one that works. Assume the value "ID" is in cell A1 so your table is in A1:E5. Place the following formula in cell A10 and copy it into cells A11 through A25. If your table isn't in A1:E5 or you don't want to put the list in A10:A25, then adjust the reference "$A$1" to whatever cell has the "ID" string (the top left corner of the table) and change "A$9" to the cell above the beginning of your list. You can't put top of the list in row 1 with this formula.

=OFFSET($A$1,TRUNC((ROW()-ROW(A$9)-1)/4,0)+1,0,1,1)& " Sub"&ROW()-ROW(A$9)-TRUNC((ROW()-ROW(A$9)-1)/4,0)*4&" "& OFFSET($A$1,TRUNC((ROW()-ROW(A$9)-1)/4,0)+1,ROW()-ROW(A$9)-TRUNC((ROW()-ROW(A$9)-1)/4,0)*4,1,1)

The formula uses "TRUNC((ROW()-ROW(A$9)-1)/4,0)+1" to determine the table row you want and "ROW()-ROW(A$9)-TRUNC((ROW()-ROW(A$9)-1)/4,0)" to determine the table column for the "Subx" value. If you have more than four columns of "Subs", change the value of 4 in the formula to the number of "Sub" columns. There are ways to determine that number on the fly but I tried to keep it simple.

How can I get the column values combination in excel

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0