Find the xth non blank value and display header

[Goauld]

Hello people!

I'm getting crazy with this one! I'm trying to merge 2 tables in 1.

So basically, this is my first table

example1.png

I have to fill this 2nd table to order my datas correctly

example2.png

So like you can see, I have to find the non blank cell, and display the header. I found several solution to do it, but my problem comes when a row has several datas. It can go up to 3 by rows. So I would like to have in my first column only the first currency, in the second column, only the second one, but if the resources doesn't have a second one, it should show nothing.
I have the feeling that I will have to use Macro for this one.

These are the different formulas I tried to solve this problem:
=INDEX(DB_Resources!$B$1:$BT$1,MATCH(0,(DB_Resources!B2:BT2),-1))
=INDEX(DB_Resources!$B$1:$BT$1,MATCH(0,(DB_Resources!B2:BT2) * (DB_Resources!B2:BT2 <> (INDEX(DB_Resources!$B$1:$BT$1,MATCH(0,(DB_Resources!B2:BT2),-1)))),-1)) (this one is closed to find the second datas but it doesn't totally work as I would like)
=INDEX(DB_Resources!$B$1:$BT$1,MATCH(TRUE,INDEX(DB_Resources!B2:BT2<>0,),0))

I would be so happy if someone can help me!

Thank you!

[Glenn Kennedy]

You have attached an image. That's not very easy to work with. Also... I'm lazy. I have to re-type your information before I can begin to address your problem. That puts me off completely. Not only that, but (depending on their choice of browser) many other members cannot even see the image.

So. Do yourself a favour and please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough). However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever).

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Make sure confidential info is removed first!!!!

[Goauld]

Ok sorry!

I attached a reduced documents, I think it should work.

My original file size is 72x72 cells

[Glenn Kennedy]

Unfortunately, you didn't tell us what your expected results are... so I made a wild guess:

R2:
=IFERROR(INDEX($1:$1,AGGREGATE(15,6,COLUMN($B2:$H2)/($B2:$H2<>""),1+INT((COLUMNS($R:R)-1)/2))),"")

S2:
=IFERROR(INDEX(2:2,AGGREGATE(15,6,COLUMN($B2:$H2)/($B2:$H2<>""),1+INT((COLUMNS($R:S)-1)/2))),"")

Select BOTH cells and drag formulae across and down

[Glenn Kennedy]

You're in BCN.... You may need ; instead of ,

[Goauld]

Omg I'm so sorry, I'm so into this problem since yesterday that I forget everything.

But yeah, it's exactly the result I wanted to find

Thank you very much, I will be more carreful next time when I will post something here.

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.