Creating a unqiue list

[Branagorn]

Hello there,

Please forgive me if you believe the answer to the following is already on the site somewhere, I've been doing some frantic googling but to no avail so far.

Im trying to create a tool that will take two bill of materials, an old and up to date version. The only user operation I want to have is the copying and pasting of this data. The spreadsheet has a dashboard that will show the comparison between the two i.e whats been added, what has been removed. The old, and new data will be stored on separate sheets.

The data has many fields and duplicate parts, but by concatenating 3 fields it can create a unique ID which can be compared across both bill of materials, if the unique ID is on both sheets, it has been carried across, if it is on one but not the other, it is either a new, or removed part.


So far I have a lot of the sheet worked out, but what I cannot do without manual handling. This manual handling is creating the master list of IDs, where I c&p the old IDS onto a new sheet, then the new ones underneath that, and then use the remove duplicates function. This list then, is every single part that was or is currently needed. The list that all the calculations are based on.

For reference the data looks like this,
[TABLE="width: 145"]
<tbody>[TR]
[TD]511VSB2200770000356[/TD]
[/TR]
[TR]
[TD]511VSB2200970000359[/TD]
[/TR]
[TR]
[TD]511VSB7202470000440[/TD]
[/TR]
[TR]
[TD]511VSB7202370000440[/TD]
[/TR]
[TR]
[TD]511VSB3202370000441[/TD]
[/TR]
[TR]
[TD]511VSB6200370000442[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

I'd like it if somebody could tell me if this absolutely cannot be done without VBA or pivot tables, then I'll give in and have to accept that.
If it does need VBA (perhaps the concatenating can be done within that also), then I'm afraid I might need a bit of baby spoon feeding, ive done it all with formulas so far.

 

[Branagorn]

** The IDs look like that. to further illustrate the old and new data

old bill of materials

[TABLE="width: 500"]
<tbody>[TR]
[TD]Fitting Number
[/TD]
[TD]Library Part Number
[/TD]
[TD]Location
[/TD]
[TD]Part Description
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 99"]
<tbody>[TR]
[TD]VSB22007
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 133"]
<tbody>[TR]
[TD="align: right"]70000356
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]corner 1
[/TD]
[TD]sofa
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 99"]
<tbody>[TR]
[TD]VSB22009
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 133"]
<tbody>[TR]
[TD="align: right"]70000356
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]corner 2
[/TD]
[TD]sofa
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 99"]
<tbody>[TR]
[TD]VSB32023
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 133"]
<tbody>[TR]
[TD="align: right"]70001444
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]middle
[/TD]
[TD]chair
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 99"]
<tbody>[TR]
[TD]VSB32023
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 133"]
<tbody>[TR]
[TD="align: right"]70002081
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]middle
[/TD]
[TD]table
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
so you can see there can be the same parts used in different locations, so to make sure we are comparing the exact same part/location these two values are concatenated together to create its unqiue ID.


New Bill

[TABLE="width: 500"]
<tbody>[TR]
[TD]Fitting Number
[/TD]
[TD]Library Part Number
[/TD]
[TD]Location
[/TD]
[TD]Part Description
[/TD]
[/TR]
[TR]
[TD]VSB22007
[/TD]
[TD]70000356
[/TD]
[TD]corner 1
[/TD]
[TD]sofa
[/TD]
[/TR]
[TR]
[TD]VSB32023
[/TD]
[TD]70001444
[/TD]
[TD]middle
[/TD]
[TD]chair
[/TD]
[/TR]
[TR]
[TD]VSB12345
[/TD]
[TD]70001234
[/TD]
[TD]upstairs
[/TD]
[TD]bed
[/TD]
[/TR]
[TR]
[TD]VBS54321
[/TD]
[TD]70004321
[/TD]
[TD]upstairs
[/TD]
[TD]chair
[/TD]
[/TR]
</tbody>[/TABLE]

So you can see that on the new bill, we will have lost the corner 2 sofa and the middle table, but gained two items upstairs.


The Dashboard I am creating looks like this.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Old Bill ID
[/TD]
[TD]Master ID List
[/TD]
[TD]New Bill ID
[/TD]
[TD]Location
[/TD]
[TD]Part Description
[/TD]
[/TR]
[TR]
[TD]VSB2200770000356
[/TD]
[TD]VSB2200770000356
[/TD]
[TD]VSB2200770000356
[/TD]
[TD]corner 1
[/TD]
[TD]sofa
[/TD]
[/TR]
[TR]
[TD]VSB2200970000356
[/TD]
[TD]VSB2200970000356
[/TD]
[TD][/TD]
[TD]corner 2
[/TD]
[TD]sofa
[/TD]
[/TR]
[TR]
[TD]VSB3202370002081
[/TD]
[TD]VSB3202370002081
[/TD]
[TD][/TD]
[TD]middle
[/TD]
[TD]table
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]VSB1234570001234
[/TD]
[TD]VSB1234570001234
[/TD]
[TD]upstairs
[/TD]
[TD]bed
[/TD]
[/TR]
[TR]
[TD]VSB3202370001444
[/TD]
[TD]VSB3202370001444
[/TD]
[TD]VSB3202370001444
[/TD]
[TD]middle
[/TD]
[TD]chair
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]VBS5432170004321
[/TD]
[TD]VBS5432170004321
[/TD]
[TD]upstairs
[/TD]
[TD]chair
[/TD]
[/TR]
</tbody>[/TABLE]


So from this its easy to use formulas, to count which cells have values across all 3 IDs (a constant part), which ones only appear in the old and master list (a removed part), or the new and master list (a completely new part).


Also notice how the fitting numbers and part numbers for the new items can be smaller than the old parts, so I like to have the master ID list ordered by the library part number (or =RIGHT(MASTERIDLIST,8).

 

[AlanY]

not the most elegant way by far, have a look if this works for you.

Col F shows 2 if appear on both old and new, 1 on old and New on new.

Excel 2013/2016
	A	B	C	D	E	F
1	Old Bill ID	Master ID List	New Bill ID	Location	Part Description
2	VSB2200770000356	VSB2200770000356	VSB2200770000356	corner 1	sofa	2
3	VSB2200970000356	VSB2200970000356		corner 2	sofa	1
4	VSB3202370002081	VSB3202370002081		middle	table	1
5		VSB1234570001234	VSB1234570001234	upstairs	bed	New
6	VSB3202370001444	VSB3202370001444	VSB3202370001444	middle	chair	2
7		VBS5432170004321	VBS5432170004321	upstairs	chair	New
Sheet1

Cell Formulas
Range	Formula
F2	=IF(ISBLANK(A2),IF(ISNUMBER(SEARCH(C2,$B:$B)),"New","x"),SEARCH(A2,B:B)+IFERROR(SEARCH(A2,C:C),0))

 

[Branagorn]

Hello Alan, thank you for your suggestion.

I have to apologise for my complicated/difficult wording of my post. It is not the analysis of the two bills I am having trouble with, but the creation of the 'Dashboard'.

I need some how to create the Master ID list, and order it by the last 8 characters. At the moment what I am doing, (which I don't want the user to have to do) is.

1. Creating IDs (by concatenating the fitting number an library part number) for both the old and new bill of materials.
2. Merging the two lists to form a 'Master ID List'
3. Removing duplicates from the Master ID List (there will be plenty of parts that show in both Bills, making the list longer than it needs to be)

The next steps help form the dashboard.

4. Sorting the Old list of IDs to show alongside the master ID list (where they exist).
5. Sorting the New list of IDs to show alongside the master ID list (where they exist).

So I think my request could be broken into two, steps 1-3, then 4&5

 

[Joe4]

Re-posted here: https://www.mrexcel.com/forum/excel-questions/1030188-vba-needed.html

In the future, please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

I am locking this original this time, instead of deleting your new post.