Help: Match Items with Parts

[Legacy 103420]

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>

149_ABC	123_ABC	BL1
132_ABC	124_ABC	BL2
142_ABC	125_ABC	BL3
124_ABC	126_ABC	BL4
130_ABC	127_ABC	BL5
134_ABC	128_ABC	BL6
137_ABC	129_ABC	BL7
140_ABC	130_ABC	BL8
143_ABC	131_ABC	BL9
139_ABC	132_ABC	BL10
128_ABC	132_ABC	BL10
133_ABC	133_ABC	BL11
145_ABC	134_ABC	BL12
136_ABC	135_ABC	BL13
	136_ABC	BL14
	137_ABC	BL15
	138_ABC	BL16
	139_ABC	BL17
	140_ABC	BL18
	141_ABC	BL19
	142_ABC	BL20
	143_ABC	BL21
	143_ABC	BL21
	144_ABC	BL22
	145_ABC	BL23
	146_ABC	BL24
	147_ABC	BL25
	148_ABC	BL26
	149_ABC	BL27

<colgroup><col width="86"><col width="86"><col width="86"></colgroup> <tbody>
[TD="width: 86"] Items w/o parts [/TD]
[TD="width: 86"] Items [/TD]
[TD="width: 86"] Parts [/TD]

</tbody>

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

124_ABC	124_ABC	BL2
128_ABC	128_ABC	BL6
130_ABC	130_ABC	BL8
132_ABC	132_ABC	BL10
133_ABC	133_ABC	BL11
134_ABC	134_ABC	BL12
136_ABC	136_ABC	BL14
137_ABC	137_ABC	BL15
139_ABC	139_ABC	BL17
140_ABC	140_ABC	BL18
142_ABC	142_ABC	BL20
143_ABC	143_ABC	BL21
145_ABC	143_ABC	BL21
149_ABC	145_ABC	BL23
	149_ABC	BL27
	123_ABC	BL1
	125_ABC	BL3
	126_ABC	BL4
	127_ABC	BL5
	129_ABC	BL7
	131_ABC	BL9
	132_ABC	BL10
	135_ABC	BL13
	138_ABC	BL16
	141_ABC	BL19
	144_ABC	BL22
	146_ABC	BL24
	147_ABC	BL25
	148_ABC	BL26

<colgroup><col width="86"><col width="86"><col width="86"></colgroup> <tbody>
[TD="width: 86"] Items w/o parts
[/TD]
[TD="width: 86"] Items [/TD]
[TD="width: 86"] Parts [/TD]

</tbody>

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

<colgroup><col width="86"><col width="86"></colgroup> <tbody>
[TD="width: 86"] 143_ABC [/TD]
[TD="width: 86"] BL21 [/TD]

</tbody>

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

<colgroup><col width="86"><col width="86"></colgroup> <tbody>
[TD="width: 86"] 143_ABC [/TD]
[TD="width: 86"] BL21 [/TD]

</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?
thnx in advanced.

 

[cyrilbrd]

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?

 

[Legacy 103420]

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?

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?

 

[webtft]

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.

 

[Legacy 103420]

YES!! plz fix this to work with column C. is near to perfect!!!

 

[webtft]

YES!! plz fix this to work with column C. is near to perfect!!!

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.