Map column headings to Row value based in value in Row/Column Cell

L

LegacyDude

New Member
Joined
Mar 18, 2024
Messages
4
Office Version
  1. 365
Platform
  1. Windows
I am trying to build a mapping table as a cross reference of what reports use a data element.
I already have the cross reference of which data elements are used in a report seen in screen shot 1. The column heading C2:I2 represents the data elements.
Trying to build a formula or VBA to show what reports B3:B are impacted by a data element as in screen shot 2.
My data is approx. 1400 columns by a few hundred rows. Doing this manually and every time the sheet changes would be extremely time consuming.

So in my example when an "X" appears in a cell for a given column therefore the Report in columns B is impacted by the element which is named as the column heading.
I tried with pivot tables and even INDEX MATCH but couldn't resolve.

Any thoughts would be appreciated.
 

Attachments

  • Screenshot 2024-03-18 160729.png
    Screenshot 2024-03-18 160729.png
    9.2 KB · Views: 17
  • Screenshot 2024-03-18 161028.png
    Screenshot 2024-03-18 161028.png
    7.8 KB · Views: 17

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
try this... assume have 4 sheets in one workbook. sheets raw, fm2, fm3, summary1. Please see the bottom about the summary and copy paste transpose. Feel free to copy paste your data, not image, if you have futher questions.

here is the example data in sheet raw. raw is where you put the X in each cell
'''cell A1 in sheet raw
DashboardReportElement 1Element 2Element 3
ABy Countryxx
ABy Regionxx
ABy Statexx
BReport 1x
BReport 2x

sheet fm2, showing the formulas. and its resulting values
''cell A1 in sheet fm2
=IF(raw!A2="","",raw!A2)=IF(raw!B2="","",raw!B2)=IF(raw!C2="","",raw!C2)=IF(raw!D2="","",raw!D2)=IF(raw!E2="","",raw!E2)=IF(raw!F2="","",raw!F2)
=IF(raw!A3="","",raw!A3)=IF(raw!B3="","",raw!B3)=IF(raw!C3="","",$B3&",")=IF(raw!D3="","",$B3&",")=IF(raw!E3="","",$B3&",")=IF(raw!F3="","",$B3&",")
=IF(raw!A4="","",raw!A4)=IF(raw!B4="","",raw!B4)=IF(raw!C4="","",$B4&",")=IF(raw!D4="","",$B4&",")=IF(raw!E4="","",$B4&",")=IF(raw!F4="","",$B4&",")
=IF(raw!A5="","",raw!A5)=IF(raw!B5="","",raw!B5)=IF(raw!C5="","",$B5&",")=IF(raw!D5="","",$B5&",")=IF(raw!E5="","",$B5&",")=IF(raw!F5="","",$B5&",")
=IF(raw!A6="","",raw!A6)=IF(raw!B6="","",raw!B6)=IF(raw!C6="","",$B6&",")=IF(raw!D6="","",$B6&",")=IF(raw!E6="","",$B6&",")=IF(raw!F6="","",$B6&",")
=IF(raw!A7="","",raw!A7)=IF(raw!B7="","",raw!B7)=IF(raw!C7="","",$B7&",")=IF(raw!D7="","",$B7&",")=IF(raw!E7="","",$B7&",")=IF(raw!F7="","",$B7&",")
=IF(raw!A8="","",raw!A8)=IF(raw!B8="","",raw!B8)=IF(raw!C8="","",raw!C8)=IF(raw!D8="","",raw!D8)=IF(raw!E8="","",raw!E8)=IF(raw!F8="","",raw!F8)
=IF(raw!A9="","",raw!A9)=IF(raw!B9="","",raw!B9)=IF(raw!C9="","",raw!C9)=IF(raw!D9="","",raw!D9)=IF(raw!E9="","",raw!E9)=IF(raw!F9="","",raw!F9)

''cell A1 in sheet fm2
DashboardReportElement 1Element 2Element 3Element 4
ABy CountryBy Country,By Country,
ABy RegionBy Region,By Region,
ABy StateBy State,By State,
BReport 1Report 1,
BReport 2Report 2,

sheet fm3, notice, i copied the formulas all the way to row 999. assumes you have no more than 999 rows.
''cell A1 in sheet fm3
=IF(raw!A2="","",raw!A2)=IF(raw!B2="","",raw!B2)=IF(raw!C2="","",raw!C2)=IF(raw!D2="","",raw!D2)=IF(raw!E2="","",raw!E2)=IF(raw!F2="","",raw!F2)
=IF(raw!A3="","",raw!A3)=IF(raw!B3="","",raw!B3)='fm2'!C3='fm2'!D3='fm2'!E3=IF(raw!F3="","",$B3&",")
=IF(raw!A4="","",raw!A4)=IF(raw!B4="","",raw!B4)=C3&'fm2'!C4=D3&'fm2'!D4=E3&'fm2'!E4=IF(raw!F4="","",$B4&",")
=IF(raw!A5="","",raw!A5)=IF(raw!B5="","",raw!B5)=C4&'fm2'!C5=D4&'fm2'!D5=E4&'fm2'!E5=IF(raw!F5="","",$B5&",")
=IF(raw!A6="","",raw!A6)=IF(raw!B6="","",raw!B6)=C5&'fm2'!C6=D5&'fm2'!D6=E5&'fm2'!E6=IF(raw!F6="","",$B6&",")
=IF(raw!A7="","",raw!A7)=IF(raw!B7="","",raw!B7)=C6&'fm2'!C7=D6&'fm2'!D7=E6&'fm2'!E7=IF(raw!F7="","",$B7&",")
=IF(raw!A8="","",raw!A8)=IF(raw!B8="","",raw!B8)=C7&'fm2'!C8=D7&'fm2'!D8=E7&'fm2'!E8
=IF(raw!A9="","",raw!A9)=IF(raw!B9="","",raw!B9)=C8&'fm2'!C9=D8&'fm2'!D9=E8&'fm2'!E9
'cell A999=C998&'fm2'!C999=D998&'fm2'!D999=E998&'fm2'!E999

''cell A1 in sheet fm3
DashboardReportElement 1Element 2Element 3
ABy CountryBy Country,By Country,
ABy RegionBy Country,By Region,By Country,By Region,
ABy StateBy Country,By Region,By Country,By State,By Region,By State,
BReport 1By Country,By Region,Report 1,By Country,By State,By Region,By State,
BReport 2By Country,By Region,Report 1,Report 2,By Country,By State,By Region,By State,
By Country,By Region,Report 1,Report 2,By Country,By State,By Region,By State,
By Country,By Region,Report 1,Report 2,By Country,By State,By Region,By State,
'cell A999By Country,By Region,Report 1,Report 2,By Country,By State,By Region,By State,

Sheet Summary1
''cell A1 in sheet Summary1
Element 1='fm3'!C999
Element 2='fm3'!D999
Element 3='fm3'!E999
Element 4
Element 5
Element 6
Element 7

''cell A1 in sheet Summary1
Element 1By Country,By Region,Report 1,Report 2,
Element 2By Country,By State,
Element 3By Region,By State,
Element 4
Element 5
Element 6
Element 7


for the summary, instead of typing in a few hundred formulas, i would set it up like this below, then copy c2:bbw3, insert new sheet, paste special, transpose.
''cell A1 in sheet fm3
DashboardReportElement 1Element 2Element 3
By Country,By Region,Report 1,Report 2,By Country,By State,By Region,By State,
 
Upvote 0
Solution
@bobaol - thanks for the response. I see how you are thinking and like it. I will definitely give this a try.
 
Upvote 0
bobaol said:
try this... assume have 4 sheets in one workbook. sheets raw, fm2, fm3, summary1. Please see the bottom about the summary and copy paste transpose. Feel free to copy paste your data, not image, if you have futher questions.

here is the example data in sheet raw. raw is where you put the X in each cell
'''cell A1 in sheet raw
DashboardReportElement 1Element 2Element 3
ABy Countryxx
ABy Regionxx
ABy Statexx
BReport 1x
BReport 2x

sheet fm2, showing the formulas. and its resulting values
''cell A1 in sheet fm2
=IF(raw!A2="","",raw!A2)=IF(raw!B2="","",raw!B2)=IF(raw!C2="","",raw!C2)=IF(raw!D2="","",raw!D2)=IF(raw!E2="","",raw!E2)=IF(raw!F2="","",raw!F2)
=IF(raw!A3="","",raw!A3)=IF(raw!B3="","",raw!B3)=IF(raw!C3="","",$B3&",")=IF(raw!D3="","",$B3&",")=IF(raw!E3="","",$B3&",")=IF(raw!F3="","",$B3&",")
=IF(raw!A4="","",raw!A4)=IF(raw!B4="","",raw!B4)=IF(raw!C4="","",$B4&",")=IF(raw!D4="","",$B4&",")=IF(raw!E4="","",$B4&",")=IF(raw!F4="","",$B4&",")
=IF(raw!A5="","",raw!A5)=IF(raw!B5="","",raw!B5)=IF(raw!C5="","",$B5&",")=IF(raw!D5="","",$B5&",")=IF(raw!E5="","",$B5&",")=IF(raw!F5="","",$B5&",")
=IF(raw!A6="","",raw!A6)=IF(raw!B6="","",raw!B6)=IF(raw!C6="","",$B6&",")=IF(raw!D6="","",$B6&",")=IF(raw!E6="","",$B6&",")=IF(raw!F6="","",$B6&",")
=IF(raw!A7="","",raw!A7)=IF(raw!B7="","",raw!B7)=IF(raw!C7="","",$B7&",")=IF(raw!D7="","",$B7&",")=IF(raw!E7="","",$B7&",")=IF(raw!F7="","",$B7&",")
=IF(raw!A8="","",raw!A8)=IF(raw!B8="","",raw!B8)=IF(raw!C8="","",raw!C8)=IF(raw!D8="","",raw!D8)=IF(raw!E8="","",raw!E8)=IF(raw!F8="","",raw!F8)
=IF(raw!A9="","",raw!A9)=IF(raw!B9="","",raw!B9)=IF(raw!C9="","",raw!C9)=IF(raw!D9="","",raw!D9)=IF(raw!E9="","",raw!E9)=IF(raw!F9="","",raw!F9)

''cell A1 in sheet fm2
DashboardReportElement 1Element 2Element 3Element 4
ABy CountryBy Country,By Country,
ABy RegionBy Region,By Region,
ABy StateBy State,By State,
BReport 1Report 1,
BReport 2Report 2,

sheet fm3, notice, i copied the formulas all the way to row 999. assumes you have no more than 999 rows.
''cell A1 in sheet fm3
=IF(raw!A2="","",raw!A2)=IF(raw!B2="","",raw!B2)=IF(raw!C2="","",raw!C2)=IF(raw!D2="","",raw!D2)=IF(raw!E2="","",raw!E2)=IF(raw!F2="","",raw!F2)
=IF(raw!A3="","",raw!A3)=IF(raw!B3="","",raw!B3)='fm2'!C3='fm2'!D3='fm2'!E3=IF(raw!F3="","",$B3&",")
=IF(raw!A4="","",raw!A4)=IF(raw!B4="","",raw!B4)=C3&'fm2'!C4=D3&'fm2'!D4=E3&'fm2'!E4=IF(raw!F4="","",$B4&",")
=IF(raw!A5="","",raw!A5)=IF(raw!B5="","",raw!B5)=C4&'fm2'!C5=D4&'fm2'!D5=E4&'fm2'!E5=IF(raw!F5="","",$B5&",")
=IF(raw!A6="","",raw!A6)=IF(raw!B6="","",raw!B6)=C5&'fm2'!C6=D5&'fm2'!D6=E5&'fm2'!E6=IF(raw!F6="","",$B6&",")
=IF(raw!A7="","",raw!A7)=IF(raw!B7="","",raw!B7)=C6&'fm2'!C7=D6&'fm2'!D7=E6&'fm2'!E7=IF(raw!F7="","",$B7&",")
=IF(raw!A8="","",raw!A8)=IF(raw!B8="","",raw!B8)=C7&'fm2'!C8=D7&'fm2'!D8=E7&'fm2'!E8
=IF(raw!A9="","",raw!A9)=IF(raw!B9="","",raw!B9)=C8&'fm2'!C9=D8&'fm2'!D9=E8&'fm2'!E9
'cell A999=C998&'fm2'!C999=D998&'fm2'!D999=E998&'fm2'!E999

''cell A1 in sheet fm3
DashboardReportElement 1Element 2Element 3
ABy CountryBy Country,By Country,
ABy RegionBy Country,By Region,By Country,By Region,
ABy StateBy Country,By Region,By Country,By State,By Region,By State,
BReport 1By Country,By Region,Report 1,By Country,By State,By Region,By State,
BReport 2By Country,By Region,Report 1,Report 2,By Country,By State,By Region,By State,
By Country,By Region,Report 1,Report 2,By Country,By State,By Region,By State,
By Country,By Region,Report 1,Report 2,By Country,By State,By Region,By State,
'cell A999By Country,By Region,Report 1,Report 2,By Country,By State,By Region,By State,

Sheet Summary1
''cell A1 in sheet Summary1
Element 1='fm3'!C999
Element 2='fm3'!D999
Element 3='fm3'!E999
Element 4
Element 5
Element 6
Element 7

''cell A1 in sheet Summary1
Element 1By Country,By Region,Report 1,Report 2,
Element 2By Country,By State,
Element 3By Region,By State,
Element 4
Element 5
Element 6
Element 7


for the summary, instead of typing in a few hundred formulas, i would set it up like this below, then copy c2:bbw3, insert new sheet, paste special, transpose.
''cell A1 in sheet fm3
DashboardReportElement 1Element 2Element 3
By Country,By Region,Report 1,Report 2,By Country,By State,By Region,By State,
Click to expand...
@bobaol , this worked perfectly. However I do want to point out a couple things in your example that should be corrected for future reference.
In tab fm3 column F

sheet fm3, notice, i copied the formulas all the way to row 999. assumes you have no more than 999 rows.
''cell A1 in sheet fm3
=IF(raw!A2="","",raw!A2)=IF(raw!B2="","",raw!B2)=IF(raw!C2="","",raw!C2)=IF(raw!D2="","",raw!D2)=IF(raw!E2="","",raw!E2)=IF(raw!F2="","",raw!F2)
=IF(raw!A3="","",raw!A3)=IF(raw!B3="","",raw!B3)='fm2'!C3='fm2'!D3='fm2'!E3='fm2'!F3
=IF(raw!A4="","",raw!A4)=IF(raw!B4="","",raw!B4)=C3&'fm2'!C4=D3&'fm2'!D4=E3&'fm2'!E4=F3&'fm2'!F4
=IF(raw!A5="","",raw!A5)=IF(raw!B5="","",raw!B5)=C4&'fm2'!C5=D4&'fm2'!D5=E4&'fm2'!E5=F4&'fm2'!F5
=IF(raw!A6="","",raw!A6)=IF(raw!B6="","",raw!B6)=C5&'fm2'!C6=D5&'fm2'!D6=E5&'fm2'!E6=F5&'fm2'!F6
=IF(raw!A7="","",raw!A7)=IF(raw!B7="","",raw!B7)=C6&'fm2'!C7=D6&'fm2'!D7=E6&'fm2'!E7=F6&'fm2'!F7
=IF(raw!A8="","",raw!A8)=IF(raw!B8="","",raw!B8)=C7&'fm2'!C8=D7&'fm2'!D8=E7&'fm2'!E8=F7&'fm2'!F8
=IF(raw!A9="","",raw!A9)=IF(raw!B9="","",raw!B9)=C8&'fm2'!C9=D8&'fm2'!D9=E8&'fm2'!E9=F8&'fm2'!F9
'cell A999=C998&'fm2'!C999=D998&'fm2'!D999=E998&'fm2'!E999=F998&'fm2'!F999

Then after creating summary tab, copy row 1 from sheet raw and paste to row 1 in summary.
Copy row 999 from tab fm3 and paste values to row 2 in summary.
Copy summary rows 1 & 2 then paste transpose to summary!A4

Thanks again.
 
Upvote 0
If all you want as output is:

20240329 PQ Unpivot LegacyDude.xlsx
NO
1ElementReport
2Element 1By Country, By Region
3Element 2By Country, By State
4Element 5By Country, Report 1
5Element 3By Region, By State
6Element 4By Region, By State, Report 2
7Element 6Report 1
8Element 7Report 1, Report 2
Sheet1


The that is pretty straight forward using Power Query

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Dashboard", "Report"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Attribute"}, {{"Rpt", each _, type table [Dashboard=nullable text, Report=nullable text, Attribute=text, Value=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Rpt][Report]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Rpt"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute", "Element"}, {"Custom", "Report"}})
in
    #"Renamed Columns"

1711672920360.png


My Test data as XL2BB

20240329 PQ Unpivot LegacyDude.xlsx
ABCDEFGHI
1DashboardReportElement 1Element 2Element 3Element 4Element 5Element 6Element 7
2ABy CountryXXX
3ABy RegionXXX
4ABy StateXXX
5BReport 1XXX
6BReport 2XX
Sheet1
 
Upvote 0
Alex Blakenburg said:
If all you want as output is:

20240329 PQ Unpivot LegacyDude.xlsx
NO
1ElementReport
2Element 1By Country, By Region
3Element 2By Country, By State
4Element 5By Country, Report 1
5Element 3By Region, By State
6Element 4By Region, By State, Report 2
7Element 6Report 1
8Element 7Report 1, Report 2
Sheet1


The that is pretty straight forward using Power Query

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Dashboard", "Report"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Attribute"}, {{"Rpt", each _, type table [Dashboard=nullable text, Report=nullable text, Attribute=text, Value=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Rpt][Report]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Rpt"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute", "Element"}, {"Custom", "Report"}})
in
    #"Renamed Columns"

View attachment 109162

My Test data as XL2BB

20240329 PQ Unpivot LegacyDude.xlsx
ABCDEFGHI
1DashboardReportElement 1Element 2Element 3Element 4Element 5Element 6Element 7
2ABy CountryXXX
3ABy RegionXXX
4ABy StateXXX
5BReport 1XXX
6BReport 2XX
Sheet1
Click to expand...

Alex,

I did look at Power Query but not proficient in it so I will give this a try and learn something. Thanks.
 
Upvote 0
Let me know if you want me to step you through either copying in the code I gave you or a step by step walk through of the how to do it in PQ.
The only part that needed a manual tweak was that the Grouped Row step creates a column containing a table and in the Added Custom (Column) step only lets you click on the column name and you then need to add to that the actual field name in that mini table ie in [Rpt][Report] in the dialogue box where I selected Rpt I typed in the [Report} part.
PS: PQ will automatically add a Change Type step - make sure you delete that step.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
VBA to create range name based on value of cells in the range?
Replies
3
Views
246
SeniorNewbie
SeniorNewbie
R
  • Question Question
Spill array that only shows rows that have value in certain cells
Replies
1
Views
127
Alex Blakenburg
A
ChrisMac1
  • Question Question
VBA - Compare specific data in one column on one sheet, with the same column on 4 other sheets, copy specific cells if match found.
Replies
1
Views
390
ChrisMac1
ChrisMac1
A
  • Question Question
Take screenshots & simultaneously capture the link, place it into an Excel file which is linked to a FileMaker/Claris db.
Replies
3
Views
389
Dan_W
D
T
  • Question Question
count cells based on the row and column headings in excel
Replies
3
Views
485
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,894
Messages
6,175,250
Members
452,623
Latest member
Techenthusiast

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