arrange complex excel data from different worksheet and sort into one database

Hi, Soberguy,

> Do you just need to return columns M, N, and O, based upon the FC and position criteria? If so, I can get you a working formula pretty quick.

Not quite sure I understand your question correctly but I think it is a yes. I want to arrange data according to FC and position.


> And....is each position truly unique? Or would one position be repeated in the various categories? That could make things more fun.

hahaha, I have my share of fun already. so, the answer is -> the position is unique.

> How "static" is Column A? Do you frequently update the positions and titles and such?

most of the position is static but , on average, 1-2 positions were added or deleted every 3 months.

let me know if you need any clarification and thank you for your help.

OK, gimme about 15 more minutes. Halfway there.

Ok, there are notes to follow!

Formula:

=IF(ISERROR(INDEX(Sheet1!$A$4:$CJ$200,MATCH($A7,Sheet1!$A$7:$A$200,0)+3,MATCH(M$5,Sheet1!$5:$5,0))),IF(ISERROR(INDEX(Sheet2!$A$4:$CJ$200,MATCH($A7,Sheet2!$A$7:$A$200,0)+3,MATCH(M$5,Sheet2!$5:$5,0))),"",INDEX(Sheet2!$A$4:$CJ$200,MATCH($A7,Sheet2!$A$7:$A$200,0)+3,MATCH(M$5,Sheet2!$5:$5,0))),INDEX(Sheet1!$A$4:$CJ$200,MATCH($A7,Sheet1!$A$7:$A$200,0)+3,MATCH(M$5,Sheet1!$5:$5,0)))

It looks ugly as a formula, but it works ;-)

Enter that on M7 on Sheet3 and you should be good to go.

The ever important notes:
I would highly recommend setting up a named range for the data on Sheet1 and Sheet2. You can pretty easily set up a Table1 named range and have it refer to $A$4 through wherever the end of your Table is. The you can replace all Sheet1!$A$4:$CJ$200 with Table1. Makes life a lot easier, at least for me.

I could ramble on and on about it and how it works, but if you have questions or issues, just give a holler.

ok, something is not right. I can't see anything in your post until I hit 'reply with quote'. (not sure if it's my computer or something with the forum)

anyway, I repost your formula here just to make it visible.

I tried your formula with the sample file and it's working. Can you also help me how can I make the cell with 'o' value looks blank?

I will try it on the real worksheet and feedback. Thank you!


Formula:

=IF(ISERROR(INDEX(Sheet1!$A$4:$CJ$200,MATCH($A7,Sheet1!$A$7:$A$200,0)+3,MATCH(M$5,Sheet1!$5:$5,0))),IF(ISERROR(INDEX(Sheet2!$A$4:$CJ$200,MATCH($A7,Sheet2!$A$7:$A$200,0)+3,MATCH(M$5,Sheet2!$5:$5,0))),"",INDEX(Sheet2!$A$4:$CJ$200,MATCH($A7,Sheet2!$A$7:$A$200,0)+3,MATCH(M$5,Sheet2!$5:$5,0))),INDEX(Sheet1!$A$4:$CJ$200,MATCH($A7,Sheet1!$A$7:$A$200,0)+3,MATCH(M$5,Sheet1!$5:$5,0)))

It looks ugly as a formula, but it works ;-)

Enter that on M7 on Sheet3 and you should be good to go.

The ever important notes:
I would highly recommend setting up a named range for the data on Sheet1 and Sheet2. You can pretty easily set up a Table1 named range and have it refer to $A$4 through wherever the end of your Table is. The you can replace all Sheet1!$A$4:$CJ$200 with Table1. Makes life a lot easier, at least for me.

I could ramble on and on about it and how it works, but if you have questions or issues, just give a holler

Maybe someone else can come along and help with that. On sheet2, if you put =N9 the value returned is 0. But you can put =if(isblank(n9),"blank","something") it returns blank. Shrug

Select the columns with the 0s and:
On the Format menu, click Cells, and then click the Number tab.
In the Category list, click Custom.
In the Type box, type 0;-0;;@

No clue what that does or why that works, but I verified it does work. Grabbed it from here