LOOKUP with Duplicate Values, so go to next instance

C

Cat129

Board Regular
Joined
Oct 20, 2012
Messages
96
I'm not too sure how to explain this one so will do my best. I have a set of data (parts lists) approx 4000 rows on a worksheet, this is the last weeks data. New data in generated each week, and we need to transfer certain data from last weeks data into this weeks. The new data that is generated goes into a new worksheet within columns A:V, and last weeks data that needs to be moved across is in rows W:BW. Columns A:V are also full of data which is what I can use for referencing

My first thought was to use a VLOOKUP or INDEX and MATCH, but I have data that is identical in rows A:V on this weeks data and last weeks data, but the information from W:BW is different.

So for example we may use the same bushing in multiple assemblies (data shown in columns A:V) but the added information in W:BW may be different, because of different suppliers / manufacturing processes.

Using one of the lookup methods above will only return the top value is finds in last weeks data (columns A:V), what I want it to do, it understand it has already found this value once and then continue to look down last weeks data to find the next instance of this within A:V, and copy the data from W:BW into this weeks data set.

Or to look at the rows above (which I dont think is possible) to see its assembly number and match the rows up that way

Can anyone help me?

Thanks,
Cat
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
Cat129 said:
Or to look at the rows above (which I dont think is possible) to see its assembly number and match the rows up that way
Click to expand...

It is possible, but maybe not the best solution depending on how your data looks. Care to post a sample?
 
Last edited:
Upvote 0
Unfortunately I cant post the data because its work, but I will try to create a dummy

Pretend data from 'last week'
[TABLE="class: grid, width: 494"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 5"]Pretend Columns A:V[/TD]
[TD="colspan: 3"]Pretend Columns W:BW[/TD]
[/TR]
[TR]
[TD]Part Number[/TD]
[TD]Revision[/TD]
[TD]BOM Level[/TD]
[TD]Qty[/TD]
[TD]Description[/TD]
[TD]Supplier 1[/TD]
[TD]Currency[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]Assembly 1[/TD]
[TD] [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD]Assembly 1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]12345-001[/TD]
[TD] [/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD]Panel[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]12345-002[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD]Nut [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]12345-003[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD]Bond Stud[/TD]
[TD]Bob[/TD]
[TD]EUR[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Assembly 2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD]Assembly 2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]12345-004[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD]Bush[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]12345-005[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD]Rod[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]12345-003[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD]Bond Stud[/TD]
[TD]Dave[/TD]
[TD]GBP[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]12345-006[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD]Screw[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

Pretend 'this weeks' data
[TABLE="class: grid, width: 329"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 5"]Pretend Columns A:V[/TD]
[/TR]
[TR]
[TD]Part Number[/TD]
[TD]Revision[/TD]
[TD]BOM Level[/TD]
[TD]Qty[/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD]Assembly 2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD]Assembly 2[/TD]
[/TR]
[TR]
[TD]12345-004[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD]Bush[/TD]
[/TR]
[TR]
[TD]12345-005[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD]Rod[/TD]
[/TR]
[TR]
[TD]12345-003[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD]Bond Stud[/TD]
[/TR]
[TR]
[TD]12345-006[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD]Screw[/TD]
[/TR]
[TR]
[TD]Assembly 1[/TD]
[TD] [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD]Assembly 1[/TD]
[/TR]
[TR]
[TD]12345-001[/TD]
[TD] [/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD]Panel[/TD]
[/TR]
[TR]
[TD]12345-002[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD]Nut [/TD]
[/TR]
[TR]
[TD]12345-003[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD]Bond Stud[/TD]
[/TR]
</tbody>[/TABLE]

I have used Bond Studs as the example above

I need to do some sort of lookup that will pull the data from 'last week' and copy it to the corresponding data in 'this week'. I have also swapped the assemblies around as this can happen in our data. This is why I would like it to be able to search upwards to find the assembly (will always been the BOM Level below, i.e. if the part is 3, its owning assembly will be 2) and not just take the top value.

If this isn't possible then let me know, I feel we are trying to make excel behave like a database and im not sure this is possible.

Thanks for your help
Cat
 
Upvote 0
How are you matching: by revision#, relative positions, or some other way?
 
Last edited:
Upvote 0
A mixture of everything, it depends on what has happend to the part

If there is no change, then part number & description
If rev has changed, then the part number with the revision removed

The only constants are the columns that I gave you in the examples, and any information you can pull from these by using various other formulas.

As an example below is the lookup I am using or all cells that do not have a duplicate somewhere else.

=IF($V2>1,"",IF($U2="No Change",INDEX('Last Week'!X:X,MATCH($K2,'Last Week'!$K:$K,0),1),IF($U2="Description Change",INDEX('Last Week'!X:X,MATCH($B2,'Last Week'!$B:$B,0),1),IF($U2="Removed",INDEX('Last Week'!$X:$X,MATCH($B2,'Last Week'!$B:$B,0),1),IF($U2="New Part","",INDEX('Last Week'!X:X,MATCH($L2,'Last Week'!$L:$L,0),1))))))

K = Concatenate - Part Number, Description
B = Part Number
L = Part Number without Revision

Cant use row position as the data is dynamic, and will change in the next update
 
Last edited:
Upvote 0
If you can use the Assembly # as a search reference then:


Excel 2010
ABCDEFGHI
1AssemblyPart NumberRevisionBOM LevelQtyDescriptionSupplier 1CurrencyCost
2Assembly 1Assembly 121Assembly 1
3Assembly 112345-00131Panel
4Assembly 112345-0025831Nut
5Assembly 112345-003931Bond StudBobEUR2
6Assembly 2Assembly 2721Assembly 2
7Assembly 212345-004731Bush
8Assembly 212345-005731Rod
9Assembly 212345-003631Bond StudDaveGBP4
10Assembly 212345-006331Screw
11
12
13
14AssemblyPart NumberRevisionBOM LevelQtyDescription
15Assembly 2Assembly 2721Assembly 2
16Assembly 212345-004731Bush
17Assembly 212345-005731Rod
18Assembly 212345-003631Bond Stud
19Assembly 212345-006331Screw
20Assembly 1Assembly 121Assembly 1
21Assembly 112345-00131Panel
22Assembly 112345-0025831Nut
23Assembly 112345-003931Bond Stud
Sheet19 (2)


is one method (the helper column can be added by formula, no need to type out)
 
Upvote 0
Ah, I understand, this was a thought we also had, but you may be adding multiple 'Bond Studs' to one assembly, and the one with the data is now sitting in the middle of the ones you just added.

I promise I'm not trying to create problems, but we are so stuck on how this would be done.
 
Upvote 0
You must log in or register to reply here.

Similar threads

ChrisMac1
  • Question Question
VBA - Compare specific data in one column on one sheet, with the same column on 4 other sheets, copy specific cells if match found.
Replies
1
Views
432
ChrisMac1
ChrisMac1
P
  • Question Question
Automating copying of columns within same sheet in excel using VBA.
Replies
1
Views
660
MountainFog
M
J
  • Question Question
VBA or formula to move data in last used column to column A
Replies
5
Views
207
Peter_SSs
Peter_SSs
A
  • Question Question
Changing row number dynamically in link to another file
Replies
1
Views
216
AlphaFrog
AlphaFrog
M
  • Question Question
Conditional Formatting/Moving Rows Based on Matching and Opposite Data
Replies
1
Views
277
Madam_Xavier
M

Forum statistics

Threads
1,224,747
Messages
6,180,712
Members
452,995
Latest member
isldboy

Share this page

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
Back
Top