Look values in range cells and copy their headers

F_Ribeiro

New Member
Joined
Jan 14, 2021
Messages
32
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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
How about
Excel Formula:
=TEXTJOIN(" | ",,FILTER($B$1:$H$1,(B2:H2<>"")*(B2:H2<>9999)))
 
Upvote 0
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:
Upvote 0
In your language it should be
Excel Formula:
=UNIRTEXTO(" | ";;FILTRO($B$1:$H$1;(B2:H2<>"")*(B2:H2<>9999)))
 
Upvote 0
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)))
 
Upvote 0
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
 
Upvote 0
Solution
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!
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

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