Look values in range cells and copy their headers

F_Ribeiro

New Member
Joined
Jan 14, 2021
Messages
28
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello everybody!

There's something I have never been able to do before and I would like your help to resolve it. And what I realized is not as easy as it looks.

In this table below, I intend to go through row by row in columns B to H. Whenever a value other than 9999 and blank is identified, the cell in column A is filled with the column title that contains that value. In summary, I want to fill the Colors column with the names of the colors using VBA.

In addition, if there is more than one value in the line other than 9999 or blank, I would like all titles to be filled in and separated by a straight bar. Example: Yellow | Brown | Green

Anyway, there is an image of this table, a manual work. I tried to use code but the result was frustrating, hahahaha ...

I'll be waiting, thank you for your attention.


f1.PNG
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,517
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=TEXTJOIN(" | ",,FILTER($B$1:$H$1,(B2:H2<>"")*(B2:H2<>9999)))
 

F_Ribeiro

New Member
Joined
Jan 14, 2021
Messages
28
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello, apologies for the delay.

Well, I use Excel PT-BR and when I tried to correct the formula accordingly my idiom, for some reason did not work. Not only did I correct the formula title, but also the ";" in syntax, which the Portuguese system uses.

What would be the syntax for using this formula as WorksheetFunction in VBA? I tried to do this since the editor requires formatting in English, and I still couldn't.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,517
Office Version
  1. 365
Platform
  1. Windows
In your language it should be
Excel Formula:
=UNIRTEXTO(" | ";;FILTRO($B$1:$H$1;(B2:H2<>"")*(B2:H2<>9999)))
 

F_Ribeiro

New Member
Joined
Jan 14, 2021
Messages
28
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

I can't believe... the Excel it's not recognizing the "FILTRO" function.

Excel suggests "FILTROXML", but not "FILTRO".
In your language it should be
Excel Formula:
=UNIRTEXTO(" | ";;FILTRO($B$1:$H$1;(B2:H2<>"")*(B2:H2<>9999)))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,517
Office Version
  1. 365
Platform
  1. Windows
Ok try
Excel Formula:
=TEXTJOIN(" | ",,IF((B2:H2<>"")*(B2:H2<>9999),$B$1:$H$1,""))
Excel Formula:
=UNIRTEXTO(" | ";;SE((B2:H2<>"")*(B2:H2<>9999);$B$1:$H$1;""))
This will probably need to be confirmed with Ctrl Shift Enter, rather than just Enter
 
Solution

F_Ribeiro

New Member
Joined
Jan 14, 2021
Messages
28
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Ok try
Excel Formula:
=TEXTJOIN(" | ",,IF((B2:H2<>"")*(B2:H2<>9999),$B$1:$H$1,""))
Excel Formula:
=UNIRTEXTO(" | ";;SE((B2:H2<>"")*(B2:H2<>9999);$B$1:$H$1;""))
This will probably need to be confirmed with Ctrl Shift Enter, rather than just Enter
GREAT!

That was difficult, I didn't think I had to use an array formula, KKKKKKKKKKKKKK!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,505
Messages
5,625,199
Members
416,080
Latest member
blemon

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
Top