Excel Pivot Tables: How to flatten a cross tab table (aka unpivot / reverse pivot in Excel 2010)
Why Stack Overflow recommends it
Stack Overflow users link it when answering questions about turning an Excel matrix or cross tab into one row per entry, since it walks through the unpivot trick step by step.
Recommended by 4 people across 4 different months, 2014 to 2016.
What it is
A short screen-recorded Excel tutorial showing how to flatten (unpivot or reverse pivot) a cross tab table into a normalized data table. It uses the old PivotTable and PivotChart Wizard with multiple consolidation ranges, reached via a keyboard shortcut in Excel 2007 and 2010. Presented by Victor Chan.
What people said
Recommends Kim Hall · 2014-09 · Stack Overflow · on “How do transform a "matrix"-table to one line for each entry in excel”
I know that it was answer years ago, BUT there is a much easier way to flatten the pivot table. This is also called the unpivot or reverse pivot. see this: [LINK] or this: [this video]
Recommends Stefan Steiger · 2015-08 · Stack Overflow · on “Unpivot an Excel matrix/pivot-table?”
Oh, well, it's a little complicated. One of the problems is, the wizard-callup shortcuts don't work in non-english versions of excels (damn, at home I would have the English version, but here at work...) Here's a good video: [this video] But youtube videos can be deleted, so to make it a solid SO answer: First, you need to go to "Options", and add the menuband-item "Pivot table and PivotChart Wizard". [LINK] [LINK] Create a multiple consolidation pivot table [LINK] and use the custom variant [LINK] and select the range, and in new work sheet [LINK] then delete rows and columns fields [LINK] Double click on the NUMBER (54 in the picture) [LINK] and excel will give you the halfway normalized data.
Recommends Scott Holtzman · 2015-12 · Stack Overflow · on “Excel Return Row and Column Headers of Nonzero Values in Table VBA”
A possible Non-VBA solution would involve using a Reverse Pivot [this video] to flatten your cross-tab data, then filtering on where Value > 0. Then you will have all Items in each Store that are > 0 in amount. End Result is like this: [LINK] For a VBA solution, give this a shot. It copies to column K of each row, based on your example table. Sheets("NA_CM1").Select ActiveSheet.ListObjects("ItI_COMPLETE_NACM1").Range.Select For Each cell In Range("ItI_COMPLETE_NACM1") If cell.Value <> 0 Then Dim sValue As String sValue = Cells(cell.Row, 11).Value2 If Len(sValue) = 0 Then Cells(cell.Row, 11).Value = Cells(cell.Row, 1).Value2 & "," & Cells(1, cell.Column).Value2 Else Cells(cell.Row, 11).Value = sVa
Recommends Ralph · 2016-08 · Stack Overflow · on “How to hlookup from one worksheet to another and repeat for multiple rows”
Sound like a typical case for `Unpivot`. You might want to google that. Here are two examples which should work for you: [LINK] OR here (with a YouTube video): [this video]