Findind the correct Line and later finding all non blank value.

[waiT_]

Hi, I am looking for something that can replace A3 and A4~A5 formulas, and be used in a table if way more columns compared to the example.

What it needs to do is Look for the LINE A1 is in the table, and then return the first non blank occurance header on a3, in a4 it will give the second non blank occurance in the line... and so on. b3,b4... Would return the Actual value, and for that I use =IFERROR(VLOOKUP(A1;TABLE;MATCH(A4;TABLE[#Headers];0));"")

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Name[/TD]
[TD]Column1[/TD]
[TD]Column2[/TD]
[TD]Column3[/TD]
[TD]Column4[/TD]
[/TR]
[TR]
[TD]Column2
[/TD]
[TD]XX[/TD]
[TD][/TD]
[TD]Name1[/TD]
[TD][/TD]
[TD]XX[/TD]
[TD][/TD]
[TD]YY[/TD]
[/TR]
[TR]
[TD]Column4[/TD]
[TD]YY[/TD]
[TD][/TD]
[TD]Name2[/TD]
[TD][/TD]
[TD][/TD]
[TD]ZZ[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula on A3
=IF(VLOOKUP(A1;TABLE;1;0)>0;TABLE[[#Headers];[Column1]];IF(VLOOKUP(A1;TABLE;2;0)>0;TABLE[[#Headers];[Column2]];IF(VLOOKUP(A1;TABLE;3;0)>0;TABLE[[#Headers];[Column3]];IF(VLOOKUP(A1;TABLE;4;0)>0;TABLE[[#Headers];[Column4]];""))))

Formula on A4
=IFERROR(IF((A1;TABLE;(MATCH(A3;TABLE[#Headers];0)+1))>0;VLOOKUP(A3;TABLE[#Headers];(MATCH(A3;TABLE[#Headers];0)+1);1);,IF(VLOOKUP(A3;TABLE[#Headers];(MATCH(A3;TABLE[#Headers];0)+2))>0;VLOOKUP(A3;TABLE[#Headers];(MATCH(A3;TABLE[#Headers];1)+2);1);IF(VLOOKUP(A3;TABLE;(MATCH(A3;TABLE[#Headers];1)+3);1)>0;VLOOKUP(A3;TABLE[#Headers];(MATCH(A3;TABLE[#Headers];1)+3);1);"")));"")

Formula on A5
=IFERROR(IF((A1;TABLE;(MATCH(A3;TABLE[#Headers];0)+1))>0;VLOOKUP(A4;TABLE[#Headers];(MATCH(A4;TABLE[#Headers];0)+1);1);,IF(VLOOKUP(A4;TABLE[#Headers];(MATCH(A4;TABLE[#Headers];0)+2))>0;VLOOKUP(A4;TABLE[#Headers];(MATCH(A4;TABLE[#Headers];1)+2);1);IF(VLOOKUP(A4;TABLE;(MATCH(A4;TABLE[#Headers];1)+3);1)>0;VLOOKUP(A4;TABLE[#Headers];(MATCH(A4;TABLE[#Headers];1)+3);1);"")));"")

Sorry for the format, Its my first post and my english is rusty.

 

[Aladin Akyurek]

Care to post a representative small sample (input) along with the desired output?

 

[waiT_]

Since I dont know much about formating here and I cant attach my own excel file, I will tyr my best.

This would be the table with my data

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Value1[/TD]
[TD]Value2[/TD]
[TD]Value3[/TD]
[TD]Value4[/TD]
[TD]Value5[/TD]
[/TR]
[TR]
[TD]Jorge[/TD]
[TD]$10[/TD]
[TD][/TD]
[TD]$5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Antony[/TD]
[TD]$8[/TD]
[TD]$3[/TD]
[TD]$5[/TD]
[TD]$9[/TD]
[TD]$19[/TD]
[/TR]
[TR]
[TD]Maria[/TD]
[TD][/TD]
[TD]$12[/TD]
[TD][/TD]
[TD]$5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Potato[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]$5[/TD]
[/TR]
</tbody>[/TABLE]

In another sheet, I would have a place to select a Name from the Name Column

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name to Search[/TD]
[/TR]
[TR]
[TD]Maria[/TD]
[/TR]
</tbody>[/TABLE]

And with that Name these results would appear(The values and its headers of the Line Maria is IN)

[TABLE="width: 500"]
<tbody>[TR]
[TD]Value2[/TD]
[TD]$12[/TD]
[/TR]
[TR]
[TD]Value4[/TD]
[TD]$5[/TD]
[/TR]
</tbody>[/TABLE]

If I changed the Name Like this.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name to Search[/TD]
[/TR]
[TR]
[TD]Potato[/TD]
[/TR]
</tbody>[/TABLE]

I would get

[TABLE="width: 500"]
<tbody>[TR]
[TD]Value5[/TD]
[TD]$5[/TD]
[/TR]
</tbody>[/TABLE]

Changing Again

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name to Search[/TD]
[/TR]
[TR]
[TD]Antony[/TD]
[/TR]
</tbody>[/TABLE]

Output

[TABLE="width: 500"]
<tbody>[TR]
[TD]Value1[/TD]
[TD]$8[/TD]
[/TR]
[TR]
[TD]Value2[/TD]
[TD]$3[/TD]
[/TR]
[TR]
[TD]Value3[/TD]
[TD]$5[/TD]
[/TR]
[TR]
[TD]Value4[/TD]
[TD]$9[/TD]
[/TR]
[TR]
[TD]Value5[/TD]
[TD]$19[/TD]
[/TR]
</tbody>[/TABLE]

And so on... For unlimited amount of columns.

Thank you in advance. I hope I was capable of explaining what I want to achieve.

I saw your function for the 1st occurance.

=INDEX(B3:IV3,MATCH(1,INDEX(1-ISBLANK(B3:IV3),1,0),0))

And made it work for the 1st value.
But I am not smart enough to get the rest without making a big and limited function.
Thanks again.

 

[Aladin Akyurek]

Your original post contains an indication that the data you have is in an Excel Table. What follows follows this...

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Name[/TD]
[TD]Value1[/TD]
[TD]Value2[/TD]
[TD]Value3[/TD]
[TD]Value4[/TD]
[TD]Value5[/TD]
[TD][/TD]
[TD]name to search[/TD]
[TD]maria[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Jorge[/TD]
[TD]

$10​

[/TD]
[TD][/TD]
[TD]

$5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]count[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Antony[/TD]
[TD]

$8​

[/TD]
[TD]

$3​

[/TD]
[TD]

$5​

[/TD]
[TD]

$9​

[/TD]
[TD]

$19​

[/TD]
[TD][/TD]
[TD]idx[/TD]
[TD]header[/TD]
[TD]value[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Maria[/TD]
[TD][/TD]
[TD]

$12​

[/TD]
[TD][/TD]
[TD]

$5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

3​

[/TD]
[TD]Value2[/TD]
[TD]

$12​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Potato[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

$5​

[/TD]
[TD][/TD]
[TD]

5​

[/TD]
[TD]Value4[/TD]
[TD]

$5​

[/TD]
[/TR]
</tbody>[/TABLE]


A:F contains Table1, created with Insert | Table.

I2, just enter:

=COUNT(INDEX(Table1,MATCH(I1,Table1[Name],0),0))

H4, control+shift+enter, not just enter, and copy down:

=IF(ROWS($H$4:H4)<=$I$2,
  SMALL(IF(ISNUMBER(INDEX(Table1,MATCH($I$1,Table1[Name],0),0)),
  COLUMN(Table1[#Headers])-MIN(COLUMN(Table1[#Headers]))+1),ROWS($H$4:H4)),"")

I4, just enter and copy down:

=IF(H4="","",INDEX(Table1[#Headers],H4))

J4, just enter and copy down:

=IF(H4="","",INDEX(Table1,MATCH($I$1,Table1[Name],0),H4))

See the workbook that implements the foregoing: https://dl.dropboxusercontent.com/u...ne and later finding all non blank value.xlsx

 

[waiT_]

Wow, this is what I am looking for.
Since it is for someone else to use it, it would be nice if it was possible to not use the auxiliary column "idx" and I2.
I've tried to do it myself but it would only show the first value. Even when I use crtl+shift+enter.


Thank you, once again.

 

[Aladin Akyurek]

Wow, this is what I am looking for.
Since it is for someone else to use it, it would be nice if it was possible to not use the auxiliary column "idx" and I2.
I've tried to do it myself but it would only show the first value. Even when I use crtl+shift+enter.


Thank you, once again.

Those are for efficiency...

 

[waiT_]

Thank you, that worked perfectly.

Do you mind telling me a place to get started with advanced excel commands, and even begVBA?

 

[Aladin Akyurek]

Thank you, that worked perfectly.

You are welcome.

Do you mind telling me a place to get started with advanced excel commands, and even begVBA?

I'd say a place like this and some sites like contextures would certainly help.

 

[waiT_]

Hi Aladin,

I had to add Columns with information about the names in the table, and it seems to break the function.
I've tried myself to correct it but didnt succeeded.

What can I do to get the same results from the new data table?
And what should I do if I have to add more columns with information about the searched name.

Example Data.

[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]InformationA[/TD]
[TD]InformationB[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Name[/TD]
[TD][/TD]
[TD][/TD]
[TD]Value1[/TD]
[TD]Value2[/TD]
[TD]Value3[/TD]
[TD]Value4[/TD]
[TD]Value5[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Jorge[/TD]
[TD]Bla[/TD]
[TD]Blo[/TD]
[TD]

$10​

[/TD]
[TD][/TD]
[TD]

$5​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Antony[/TD]
[TD]BlaBla[/TD]
[TD][/TD]
[TD]

$8​

[/TD]
[TD]

$3​

[/TD]
[TD]

$5​

[/TD]
[TD]

$9​

[/TD]
[TD]

$19​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Maria[/TD]
[TD][/TD]
[TD]Blab[/TD]
[TD][/TD]
[TD]

$12​

[/TD]
[TD][/TD]
[TD]

$5​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Potato[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

$5​

[/TD]
[/TR]
</tbody>[/TABLE]

 

[waiT_]

I've tried to make it work more, and it seems that I was doing some mistakes.
Didnt even need to change your set up.

Thanks Aladin.