Help: Match Items with Parts

L

Legacy 103420

Guest
hi. a project im working on requires us to match items with their parts. the problem is we have a really big list of parts and some duplicates. we tried to highlight all duplicate values and then sort by cell color, but this takes a so long time! then when it is done we have to remove the duplicate values because exists duplicates which cause the rows to not lineup.

example below of original file:
<style> <!-- BODY,DIV,TABLE,THEAD,TBODY,TFOOT,TR,TH,TD,P { font-family:"Liberation Sans"; font-size:x-small } --> </style>
Items w/o partsItemsParts
149_ABC123_ABCBL1
132_ABC124_ABCBL2
142_ABC125_ABCBL3
124_ABC126_ABCBL4
130_ABC127_ABCBL5
134_ABC128_ABCBL6
137_ABC129_ABCBL7
140_ABC130_ABCBL8
143_ABC131_ABCBL9
139_ABC132_ABCBL10
128_ABC132_ABCBL10
133_ABC133_ABCBL11
145_ABC134_ABCBL12
136_ABC135_ABCBL13
136_ABCBL14
137_ABCBL15
138_ABCBL16
139_ABCBL17
140_ABCBL18
141_ABCBL19
142_ABCBL20
143_ABCBL21
143_ABCBL21
144_ABCBL22
145_ABCBL23
146_ABCBL24
147_ABCBL25
148_ABCBL26
149_ABCBL27

<colgroup><col width="86"><col width="86"><col width="86"></colgroup> <tbody>
</tbody>

after sorting:
<style> <!-- BODY,DIV,TABLE,THEAD,TBODY,TFOOT,TR,TH,TD,P { font-family:"Liberation Sans"; font-size:x-small } --> </style>
Items w/o parts
ItemsParts
124_ABC124_ABCBL2
128_ABC128_ABCBL6
130_ABC130_ABCBL8
132_ABC132_ABCBL10
133_ABC133_ABCBL11
134_ABC134_ABCBL12
136_ABC136_ABCBL14
137_ABC137_ABCBL15
139_ABC139_ABCBL17
140_ABC140_ABCBL18
142_ABC142_ABCBL20
143_ABC143_ABCBL21
145_ABC
143_ABCBL21
149_ABC145_ABCBL23
149_ABCBL27
123_ABCBL1
125_ABCBL3
126_ABCBL4
127_ABCBL5
129_ABCBL7
131_ABCBL9
132_ABCBL10
135_ABCBL13
138_ABCBL16
141_ABCBL19
144_ABCBL22
146_ABCBL24
147_ABCBL25
148_ABCBL26

<colgroup><col width="86"><col width="86"><col width="86"></colgroup> <tbody>
</tbody>

the problem is <style> <!-- BODY,DIV,TABLE,THEAD,TBODY,TFOOT,TR,TH,TD,P { font-family:"Liberation Sans"; font-size:x-small } --> </style>
143_ABCBL21

<colgroup><col width="86"><col width="86"></colgroup> <tbody>
</tbody>
<style> <!-- BODY,DIV,TABLE,THEAD,TBODY,TFOOT,TR,TH,TD,P { font-family:"Liberation Sans"; font-size:x-small } --> </style>
143_ABCBL21

<colgroup><col width="86"><col width="86"></colgroup> <tbody>
</tbody>

if we remove this as duplicate it is deleted altogether. the solution manually is to delete bottom one and merge cell and rows to the right of it up one. then all the lines are aligned properly and the lost items matched with parts. but as you see this is quite a complex problem. plus we have over 10k lines into it. sorting takes so long time... some way to better do this? :confused:
thnx in advanced.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
Hi,

Not sure if this is what you want, but have you tried to create a unique list from your column B?
Insert / Name / Define: create "Rvec" with the following formula: =ROW(Sheet1!$B$2:$B$11000)-ROW(Sheet1!$B$2)+1

Then in E1 type:
=SUM(IF(FREQUENCY(IF(B2:B11000<>"",MATCH("~"&B2:B11000,B2:B11000&"",0)),Rvec),1))
Ctrl + Shift + Enter, not just enter.
This will return the count of unique items found in Column B (till row 11000).
In E2 type "Distinct"
In E3 type:
=IF(ROWS($E$3:E3)<=$E$1,INDEX($B$2:$B$11000, SMALL(IF(FREQUENCY(IF($B$2:$B$11000<>"", MATCH("~"&$B$2:$B$11000,$B$2:$B$11000&"",0)),Rvec),Rvec), ROWS($E$3:E3))),"")

Ctrl + Shift + Enter, not just enter.
This will return all distinct (unique) values found in Column "Item".

Is this what you needed?
 
Upvote 0
cyrilbrd said:
Hi,

Not sure if this is what you want, but have you tried to create a unique list from your column B?
Insert / Name / Define: create "Rvec" with the following formula: =ROW(Sheet1!$B$2:$B$11000)-ROW(Sheet1!$B$2)+1

Then in E1 type:
=SUM(IF(FREQUENCY(IF(B2:B11000<>"",MATCH("~"&B2:B11000,B2:B11000&"",0)),Rvec),1))
Ctrl + Shift + Enter, not just enter.
This will return the count of unique items found in Column B (till row 11000).
In E2 type "Distinct"
In E3 type:
=IF(ROWS($E$3:E3)<=$E$1,INDEX($B$2:$B$11000, SMALL(IF(FREQUENCY(IF($B$2:$B$11000<>"", MATCH("~"&$B$2:$B$11000,$B$2:$B$11000&"",0)),Rvec),Rvec), ROWS($E$3:E3))),"")

Ctrl + Shift + Enter, not just enter.
This will return all distinct (unique) values found in Column "Item".

Is this what you needed?
Click to expand...
thank you for this, however it does not quite do what we need. We need it to sort the items without parts with the items which have parts. here is an example ive done manually. perhaps some macro to do this? we also don't need all the items, so can save some processing.

example of completed list:

Items w/o parts Items Parts
149_ABC 149_ABC BL27
132_ABC 132_ABC BL10
142_ABC 142_ABC BL20
124_ABC 124_ABC BL2
130_ABC 130_ABC BL8
134_ABC 134_ABC BL12
137_ABC 137_ABC BL15
140_ABC 140_ABC BL18
143_ABC 143_ABC BL21
139_ABC 139_ABC BL17
128_ABC 128_ABC BL6
133_ABC 133_ABC BL11
145_ABC 145_ABC BL23
136_ABC 136_ABC BL14


some way to do this?
 
Upvote 0
The script here will match items with items. I am not sure how you would go about matching the parts. Someone with better VBA scripting would be able to help - http://www.mrexcel.com/forum/showth...hing-entries&p=2940113&viewfull=1#post2940113

I assume you use excel 2010, as it supports the latest macro techniques.

Basically you want to match A with B, and anything on C we assume is fine so match it with B so when A gets sorted it all lines up?

I would also be interested in seeing this script if anyone knows. We have to match similar data against 26,000 lines. We use Sort A-Z and then find duplicates, remove them, and line up each row ourselves. A bit of overhead and time being consumed for such a simple task.
 
Last edited:
Upvote 0
tonmegub said:
YES!! plz fix this to work with column C. is near to perfect!!!
Click to expand...
Code: 
Sub arrangeA_B()
Dim last_row As Long
Dim my_range As Range
Dim rag, c As Range
Dim temp
    
    last_row = Range("B" & Rows.count).End(xlUp).Row
    For x = 1 To last_row
        Set c = Range("A:A").Find(Range("B" & x).Value, LookIn:=xlValues, lookat:=xlWhole)
        If Not c Is Nothing Then
            If c.Row <> x And Range("B" & x).Value <> "" Then
                If c.Offset(0, 1).Value = Range("B" & x).Value Then
                    R = MsgBox("Duplication " & Range("B" & x).Value, vbOKOnly)
                    Exit Sub
                End If
                    
                temp = c.Offset(0, 1).Value
                Temp2 = c.offset(0,2).value
                c.Offset(0, 1).Value = Range("B" & x).Value
                c.Offset(0,2).Value = Range("C" & x).Value
                Range("B" & x).Value = temp
                Range("C" & x).Value = temp2
                x = x - 1
            End If
        Else
            Range("B" & Range("B" & Rows.count).End(xlUp).Row + 1).Value = Range("B" & x).Value
            Range("C" & Range("C" & Rows.count).End(xlUp).Row + 1).Value = Range("C" & x).Value
            Range("B" & x).ClearContents
            Range("C" & x).clearcontents
        End If
    Next
    
End Sub

Here you go, for some reason it still does not work - but it's a start in the right direction. The code just infinit loops when I tried it... maybe someone can fix it or you will have better luck.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Question
Need help simplifying an Equation with 63 variants
Replies
0
Views
289
Ordonius
O
W
  • Question
Sorting and totaling of data in rows, Excel
Replies
4
Views
682
westcap
W

Forum statistics

Threads
1,215,200
Messages
6,123,611
Members
449,109
Latest member
Sebas8956

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