Consolidating multiple rows into one row with row data from some columns across page

E

Edlin59

New Member
Joined
Nov 28, 2018
Messages
7
Hello. I have multiple rows of data that I would like to consolidate into one row based on information in a number of columns. In the example 1 below I would like to consolidate all rows based on UniqueId, however would like the rows for Product & Question columns combined across the page with Answer displayed as a row under the combined columns – as per example 2.

Example 1

UniqueIdNameCol3Col4Col5Col6ProductQuestionAnswer
Name1RedDCWhiteHigh1/12/2018ProdAQ1Yes
Name1RedDCWhiteHigh1/12/2018ProdAQ2Yes
Name1RedDCWhiteHigh1/12/2018ProdAQ3Yes
Name1RedDCWhiteHigh1/12/2018ProdAQ4No
Name1RedDCWhiteHigh1/12/2018ProdAQ5No
Name1RedDCWhiteHigh1/12/2018ProdAQ6No
Name1RedDCWhiteHigh1/12/2018ProdAQ7NA
Name1RedDCWhiteHigh1/12/2018ProdAQ8No
Name1RedDCWhiteHigh1/12/2018ProdAQ9Yes
Name1RedDCWhiteHigh1/12/2018ProdAQ10No
Name1RedDCWhiteHigh1/12/2018ProdAQ11No
Name1RedDCWhiteHigh12/12/2018ProdAQ12No
Name2BlueDCWhiteHigh12/12/2018ProdAQ1No
Name2BlueDCWhiteHigh12/12/2018ProdAQ2No
Name2BlueDCWhiteHigh12/12/2018ProdAQ3Yes
Name2BlueDCWhiteHigh12/12/2018ProdAQ4No
Name2BlueDCWhiteHigh12/12/2018ProdAQ5No
Name2BlueDCWhiteHigh12/12/2018ProdAQ6No
Name2BlueDCWhiteHigh12/12/2018ProdAQ7Yes
Name2BlueDCWhiteHigh12/12/2018ProdAQ8No
Name2BlueDCWhiteHigh12/12/2018ProdAQ9Yes
Name2BlueDCWhiteHigh12/12/2018ProdAQ10No
Name2BlueDCWhiteHigh12/12/2018ProdAQ11No
Name2BlueDCWhiteHigh12/12/2018ProdAQ12No
Name3GreenAutoWhiteLow4/12/2018ProdBQ1No
Name3GreenAutoWhiteLow4/12/2018ProdBQ2No
Name3GreenAutoWhiteLow4/12/2018ProdBQ3No
Name3GreenAutoWhiteLow4/12/2018ProdBQ4No
Name3GreenAutoWhiteLow4/12/2018ProdBQ5No
Name3GreenAutoWhiteLow4/12/2018ProdBQ6No
Name3GreenAutoWhiteLow4/12/2018ProdBQ7No
Name3GreenAutoWhiteLow4/12/2018ProdBQ8No
Name3GreenAutoWhiteLow4/12/2018ProdBQ9Yes
Name3GreenAutoWhiteLow4/12/2018ProdBQ10Yes
Name3GreenAutoWhiteLow4/12/2018ProdBQ11 No
Name3GreenAutoWhiteLow4/12/2018ProdBQ12NA

<colgroup><col><col><col><col><col span="3"><col span="2"></colgroup><tbody>
</tbody>

Example 2

UniqueIdNameCol3Col4Col5Col6ProdAProdAProdAProdAProdAProdAProdAProdAProdAProdAProdAProdA
Name1RedDCWhiteHigh1/12/2018Q1Q2Q3Q4Q5Q6Q7Q8Q9Q10Q11Q12
Name2BlueDCWhiteHigh12/12/2018YesYesYesNoNoNoNANoYesNoNoNo
Name3GreenAutoWhiteLow4/12/2018NoNoYesNoNoNoYesNoYesNoNoNo

<colgroup><col><col><col><col><col span="3"><col span="4"><col span="5"><col span="2"></colgroup><tbody>
</tbody>

Help appreciated
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
your source table has error : Name1 has two different dates
example 2 doesn't make sense - compare source with your defined answer
 
Upvote 0
sandy666 said:
your source table has error : Name1 has two different dates
example 2 doesn't make sense - compare source with your defined answer
Click to expand...

"Thanks for your response. I'll try again to explain. I was trying to replicate spreadsheet without using real data. (Name1 should also have all same date that was a typo). My current worksheet has Cols A-Col O (with various headings) of data, I want to consolidate all rows for Name1, Name 2 etc into one row for each name, Name1, Name2, Name3. However, I also want to combine the Product and Question columns into one column then have the Answer displayed as a row for each name under this combined column."
 
Upvote 0
Use GoogleDrive, OneDrive or similar to share excel file and post link here in the post.
File should contain proper source and proper output what you want to achieve
 
Upvote 0
UniqueIdNameCol3Col4Col5Col6ProductQuestionAnswer
Name1RedDCWhiteHigh
01/12/2018​
ProdAQ1Yes
Name1RedDCWhiteHigh
01/12/2018​
ProdAQ2Yes
Name1RedDCWhiteHigh
01/12/2018​
ProdAQ3Yes
Name1RedDCWhiteHigh
01/12/2018​
ProdAQ4No
Name1RedDCWhiteHigh
01/12/2018​
ProdAQ5No
Name1RedDCWhiteHigh
01/12/2018​
ProdAQ6No
Name1RedDCWhiteHigh
01/12/2018​
ProdAQ7NA
Name1RedDCWhiteHigh
01/12/2018​
ProdAQ8No
Name1RedDCWhiteHigh
01/12/2018​
ProdAQ9Yes
Name1RedDCWhiteHigh
01/12/2018​
ProdAQ10No
Name1RedDCWhiteHigh
01/12/2018​
ProdAQ11No
Name1RedDCWhiteHigh
01/12/2018​
ProdAQ12No
Name2BlueDCWhiteHigh
12/12/2018​
ProdAQ1No
Name2BlueDCWhiteHigh
12/12/2018​
ProdAQ2No
Name2BlueDCWhiteHigh
12/12/2018​
ProdAQ3Yes
Name2BlueDCWhiteHigh
12/12/2018​
ProdAQ4No
Name2BlueDCWhiteHigh
12/12/2018​
ProdAQ5No
Name2BlueDCWhiteHigh
12/12/2018​
ProdAQ6No
Name2BlueDCWhiteHigh
12/12/2018​
ProdAQ7Yes
Name2BlueDCWhiteHigh
12/12/2018​
ProdAQ8No
Name2BlueDCWhiteHigh
12/12/2018​
ProdAQ9Yes
Name2BlueDCWhiteHigh
12/12/2018​
ProdAQ10No
Name2BlueDCWhiteHigh
12/12/2018​
ProdAQ11No
Name2BlueDCWhiteHigh
12/12/2018​
ProdAQ12No
Name3GreenAutoWhiteLow
04/12/2018​
ProdBQ1No
Name3GreenAutoWhiteLow
04/12/2018​
ProdBQ2No
Name3GreenAutoWhiteLow
04/12/2018​
ProdBQ3No
Name3GreenAutoWhiteLow
04/12/2018​
ProdBQ4No
Name3GreenAutoWhiteLow
04/12/2018​
ProdBQ5No
Name3GreenAutoWhiteLow
04/12/2018​
ProdBQ6No
Name3GreenAutoWhiteLow
04/12/2018​
ProdBQ7No
Name3GreenAutoWhiteLow
04/12/2018​
ProdBQ8No
Name3GreenAutoWhiteLow
04/12/2018​
ProdBQ9Yes
Name3GreenAutoWhiteLow
04/12/2018​
ProdBQ10Yes
Name3GreenAutoWhiteLow
04/12/2018​
ProdBQ11No
Name3GreenAutoWhiteLow
04/12/2018​
ProdBQ12NA

here are two tables that have no connection with each other but they probably look the way you want

UniqueIdNameCol3Col4Col5Col6Column1Column2Column3Column4Column5Column6Column7Column8Column9Column10Column11Column12Column13Column14Column15Column16Column17Column18Column19Column20Column21Column22Column23Column24
Name1RedDCWhiteHigh
01/12/2018​
ProdAProdAProdAProdAProdAProdAProdAProdAProdAProdAProdAProdAProdAProdAProdAProdAProdAProdAProdAProdAProdAProdAProdAProdA
Name2BlueDCWhiteHigh
12/12/2018​
Q1Q2Q3Q4Q5Q6Q7Q8Q9Q10Q11Q12Q1Q2Q3Q4Q5Q6Q7Q8Q9Q10Q11Q12
Name3GreenAutoWhiteLow
04/12/2018​
YesYesYesNoNoNoNANoYesNoNoNoNoNoYesNoNoNoYesNoYesNoNoNo
 
Last edited:
Upvote 0
I can see why it didn't make sense. The table below are nearly like how I want displayed. First table is correct then for second table, I'm combining Prod&Q as a header with the yes, no, na Answers in the row;
Column1Column2Column3Column4Column5Column6Column7Column8Column9Column10Column11Column12Column13Column14Column15Column16Column17Column18Column19Column20Column21Column22Column23Column24
ProdAQ1ProdAQ2ProdAQ3ProdAQ4ProdAQ5ProdAQ6ProdAQ7ProdAQ8ProdAQ9ProdAQ10ProdAQ11ProdAQ12ProdBQ1ProdBQ2ProdBQ3ProdBQ4ProdBQ5ProdBQ6ProdBQ7ProdBQ8ProdBQ9ProdBQ10ProdBQ11ProdBQ12
YesYesYesNoNoNoNANoYesNoNoNonananananananananananana
NoNoYesNoNoNoYesNoYesNoNoNonananananananananananana
nanananananananananananaNoNoNoNoNoNoNoNoYesYesNona

<colgroup><col span="2"><col span="22"></colgroup><tbody>
</tbody>
 
Upvote 0
something like this?

UniqueIdNameCol3Col4Col5Col6ProdAQ1ProdAQ2ProdAQ3ProdAQ4ProdAQ5ProdAQ6ProdAQ7ProdAQ8ProdAQ9ProdAQ10ProdAQ11ProdAQ12ProdBQ1ProdBQ2ProdBQ3ProdBQ4ProdBQ5ProdBQ6ProdBQ7ProdBQ8ProdBQ9ProdBQ10ProdBQ11ProdBQ12
Name1RedDCWhiteHigh
01/12/2018​
YesYesYesNoNoNoNANoYesNoNoNo
Name2BlueDCWhiteHigh
12/12/2018​
NoNoYesNoNoNoYesNoYesNoNoNo
Name3GreenAutoWhiteLow
04/12/2018​
NoNoNoNoNoNoNoNoYesYesNoNA
 
Upvote 0
so here is a M-code

Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"UniqueId", type text}, {"Name", type text}, {"Col3", type text}, {"Col4", type text}, {"Col5", type text}, {"Col6", type date}, {"Product", type text}, {"Question", type text}, {"Answer", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type",{"Product", "Question"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Answer")
in
    #"Pivoted Column"[/SIZE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Consolidate data from individual months by summing 2, 3 or more months together
Replies
0
Views
129
Kaleo
K
N
  • Solved
Separate One Cell Data into Multiple Rows
Replies
6
Views
774
Fluff
Fluff
B
  • Question
Create single list of value from same cells across multiple sheets
Replies
2
Views
423
BigKeyes13K
B
G
  • Question
Formula to count with multiple criteria and exclude from a separate column, then sort by count in one cell with spill
Replies
0
Views
340
Greenbehindthecells
G
P
  • Solved
Convert value if the date listed within the column is within the month going across the rows
Replies
3
Views
301
Fluff
Fluff

Forum statistics

Threads
1,214,807
Messages
6,121,679
Members
449,047
Latest member
notmrdurden

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top