HELP...not sure how to do this

TEKMSTR

Board Regular
Joined
Mar 10, 2006
Messages
73
Hello, all my efforts have failed any ideas would be greatly appreciated.

here's an example:

...A.............B
Parts.........IND
0001...........1
0002...........2
0003...........2
0004...........2
0005...........3
0006...........3
0007...........4
0008...........3
0009...........3
0010...........2

Here's how to interpret this. All parts with an IND's 2 are components found on IND 1. All IND's 3 are found on IND 2....and so on. The trick to this is for example all IND's 3....I only need the last IND 2 that is directly above it.

Here is the result:
...A.............B............C
Parts.........IND........RES
0001...........1...........0001 (IND's of 1 belong to themselves)
0002...........2...........0001
0003...........2...........0001
0004...........2...........0001
0005...........3...........0004 (only need the part # for last listed IND)
0006...........3...........0004
0007...........4...........0006
0008...........3...........0004
0009...........3...........0004
0010...........2...........0001

Each part only belongs to the last listed one above it with a lower IND. I've tried various look up formulas to no avail.

I'm thinking this might call for some kind of offset formula but I cant get it to work.

Be advised this is a small sample. In the list there can be 100's of part number with for example IND 3, so a nested IF statement that I've also tried would not work.

Thanks for any ideas.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,697
Try...

C2, copied down:

=IF(B2>1,LOOKUP(2,1/(B$2:B2=B2-1),A$2:A2),A2)

Hope this helps!
 

Forum statistics

Threads
1,136,592
Messages
5,676,690
Members
419,644
Latest member
KeelsM

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
Top