• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How can fetch column heading for each row

R

Ram Mher

Member
Dear Friends

Greetings!

I have values in ROW and heading in Column therefore i want to fetch the heading name in each row.

File is attached for reference.
 

Attachments

  • Solution required.xlsx
    108 KB · Views: 8
Hi,

you can also try this one..

=INDEX($B$1:$I$1,MATCH(TRUE,B2:I2<>"",0))--finish with CTRL+SHIFT+ENTER
OR
INDEX($B$1:$I$1,AGGREGATE(15,6,COLUMN($B$1:$I$1)-1/(B2:I2<>""),1)) with CTRL+SHIFT+ENTER

regards
Naresh
 
Hi If i put more then 2 value in same row how can i got many time same heading

example : Unique id 0075 & i am putting value there Ram 1 Shyam 2 Gopal 3 then i should got result Ram shyam gopal
 
achu said:
Hi If i put more then 2 value in same row how can i got many time same heading

example : Unique id 0075 & i am putting value there Ram 1 Shyam 2 Gopal 3 then i should got result Ram shyam gopal
Click to expand...
Check this, if it is what you want for multi-headings result.

See attached file.

Regards
Bosco
 

Attachments

  • Solution required (2).xlsx
    108 KB · Views: 22
Last edited:
Ram Mher said:
Dear Friend
May u please guide what is the use of 1 to 8 in your index formula
Click to expand...
Hi ,

Name is a named range which has been defined as follows :

=IFERROR(INDEX(Sheet2!$B$1:$I$1,N(IF(1,AGGREGATE(15,6,COLUMN(Sheet2!$B$1:$I$1)-1/Sheet2!$B2:$I2,ROW(Sheet2!$A$1:$A$8))))),"")

What this formula does is it generates an array of values from the range B1:I1 ; this is the header row , which contains the names Ram , Shyam , Radha ,.... ; where the data range B2:I9 contains some value (for each cell in the range which is not blank) , the array will contain the corresponding name from the header row ; where a cell within the data range is blank , the array will contain a blank.

What you want is a concatenation of all the elements of this array ; retrieving each individual element of the array is done by INDEX(Name , 1) , INDEX(Name , 2) , INDEX(Name , 3) ,...

Since there are 8 names in the header row , the number 8 is used ; if there were 20 names in the header row , you would have to go from INDEX(Name , 1) till INDEX(Name , 20).

Narayan
 
Nice technique. Maybe worth noting one can remove the N() function:

=IFERROR(INDEX(Sheet2!$B$1:$I$1,AGGREGATE(15,6,COLUMN(Sheet2!$B$1:$I$1)-1/Sheet2!$B2:$I2,IF(1,ROW(Sheet2!$A$1:$A$8)))),"")

That said, with only 8 names it'd be simpler and more efficient to just fill down:

=TRIM(CONCATENATE(IF(B2,B$1,)," ",IF(C2,C$1,)," ",IF(D2,D$1,)," ",IF(E2,E$1,)," ",IF(F2,F$1,)," ",IF(G2,G$1,)," ",IF(H2,H$1,)," ",IF(I2,I$1,)))
 
You must log in or register to reply here.
Back
Top