Formula to determine which column fields are marked as NOT blank and input corresponding column value in cell on Google Sheets

jw2022

New Member
Joined
Apr 28, 2022
Messages
11
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. MacOS
  3. Web
I have a spreadsheet on Google Sheets with essentially 2 tables. The below is an example of what it looks like, but my data is more complex and with a wider range.

In the first set of data or table, in the rows down I have items, and in the columns across I have categories. Each Item (row) has ticks in 3 of the categories. I want to represent this data in the second table, whereby in the rows down there are the same items, but instead the columns show the actual value (from the header) that are ticked in the first table.

As an example, Item 1 has A, C and E ticked, so in the second table, as shown I want to have the values 'A' (appear in J5), 'C' (in K5) and 'E' (in L5). Right now I've just manually typed in the category that corresponds with the tick according to the item in the row, but my original data has over 30 categories and only 3 are selected for each item (row), I want to present it so I can see the value of what is selected. Is there a formula I can use to do this, I'm thinking something along the lines of using an IF function whereby the cell is not blank?
 

Attachments

  • 1.jpg
    1.jpg
    95.8 KB · Views: 16

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Try this one in J5 then fill right and down as required.

Excel Formula:
=ARRAYFORMULA(INDEX($C$4:$I$4,SMALL(IF($C4:$I4<>"",COLUMN($C$4:$I$4)-COLUMN($C$4)+1),COLUMNS($J5:J5))))
 
Upvote 0
Solution

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

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