Mess which could be helped with some lovely helper columns / macros maybe?!

[ellison]

Hi, I'm in a pickle and I can see a way out of it, but just not how to do it, arrghhhhhhhh!

We've got a [looong] list of more than 10,000 records which has on it:
Col A: old_product ID's
Col B: their customers.
(one old_product ID can have multiple customers)


And we need to work out how to integrate our new information which is even longer which has:
[this on a separate file...]
Col A: new_product ID
Col B: its associated products
(one new_product ID can have multiple associated products).


What we'd really like is a new listing which shows:


Col A: old_product ID
Col B: their_customers
Col C: new_product ID's
Col D: multiple_associated_products


The way I'd done this on a small scale was by duplicating rows and vlookups and attempted index matches, but frankly it's a mess and I wonder if somebody in the lovely world of "mr excel" may have some better ideas?


I'm open to using helper columns, UDF's, macro's and buying people a coffee to help get a method which will work!


Best


Neil


PS just to add a further fly in the ointment, rather than doing an exact match on the new and old product_ID's, they would like the list to take into account "partial matches" where the new_product_id *contains* the old_product_ID

 

[Fluff]

Can you supply a representative list of both old & new IDs, along with an indication of which numbers "match"

 

[ellison]

Thanks Fluff, hope this helps:

File 1 = Old_product ID
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Col A: old_product_ID[/TD]
[TD]Row B: Customers[/TD]
[/TR]
[TR]
[TD]apple[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]apple[/TD]
[TD]Jane[/TD]
[/TR]
</tbody>[/TABLE]

File 2 = new_product_IDs

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Col A: new_product_ID[/TD]
[TD]Col B: associated products[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD]oranges[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD]pears[/TD]
[/TR]
</tbody>[/TABLE]


File 3 = what we are hoping to acheive:

As regards the match: as "apple" is contained in "apples", that would therefore deemed to be a match
ie. if the old_product_id is contained in (or equals) the new_product_ID, it's a match
95% of the time, the product_id's are a mixture of upper or lower case letters and numbers
So ideally, any match formula would be "non-case-dependent"
Also the product_Id's are just sometimes only letters or only numbers (so it would be good to take that into account too!)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Old_product_ID[/TD]
[TD]Customers[/TD]
[TD]New_product_ID[/TD]
[TD]Associated Products[/TD]
[/TR]
[TR]
[TD]apple[/TD]
[TD]john[/TD]
[TD]apples[/TD]
[TD]oranges[/TD]
[/TR]
[TR]
[TD]apple[/TD]
[TD]john[/TD]
[TD]apples[/TD]
[TD]pears[/TD]
[/TR]
[TR]
[TD]apple[/TD]
[TD]jane[/TD]
[TD]apples[/TD]
[TD]oranges[/TD]
[/TR]
[TR]
[TD]apple[/TD]
[TD]jane[/TD]
[TD]apples[/TD]
[TD]pears[/TD]
[/TR]
</tbody>[/TABLE]

I've explained to the powers-that-be that these lists will end up having more and more rows as a result of organising the information in this way...
But apparently that isn't as important as the right people getting a report organised in this way!!!

 

[Fluff]

We need a representative sample of your actual data, not some made up values. Bearing in mind you said

rather than doing an exact match on the new and old product_ID's, they would like the list to take into account "partial matches" where the new_product_id *contains* the old_product_ID

How do you expect anybody to manage that with the fake data you just supplied?

 

[ellison]

Sorry, I thought it may have been easier to explain using those examples. If more examples would help, please let us know


File 1 = Old_product ID
[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Col A: old_product_ID[/TD]
[TD]Row B: Customers[/TD]
[/TR]
[TR]
[TD]MC68HC11A1FN[/TD]
[TD]CRT Ltd[/TD]
[/TR]
[TR]
[TD]MC68HC11A1FN[/TD]
[TD]Eldocis SA[/TD]
[/TR]
</tbody>[/TABLE]


File 2 = new_product_IDs

[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Col A: new_product_ID[/TD]
[TD]Col B: associated products[/TD]
[/TR]
[TR]
[TD]MC68HC11a1F/SMD[/TD]
[TD]SN74LS240J[/TD]
[/TR]
[TR]
[TD]MC68HC11a1F/SMD[/TD]
[TD]SN74LS244J[/TD]
[/TR]
</tbody>[/TABLE]


File 3: what we're after

[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Old_product_ID[/TD]
[TD]Customers[/TD]
[TD]New_product_ID[/TD]
[TD]Associated Products[/TD]
[/TR]
[TR]
[TD]MC68HC11A1F[/TD]
[TD]CRT Ltd[/TD]
[TD]MC68HC11a1F/SMD[/TD]
[TD]SN74LS240J[/TD]
[/TR]
[TR]
[TD]MC68HC11A1F[/TD]
[TD]Eldocis SA[/TD]
[TD]MC68HC11a1F/SMD[/TD]
[TD]SN74LS244J[/TD]
[/TR]
[TR]
[TD]MC68HC11A1F[/TD]
[TD]CRT Ltd[/TD]
[TD]MC68HC11a1F/SMD[/TD]
[TD]SN74LS240J[/TD]
[/TR]
[TR]
[TD]MC68HC11A1F[/TD]
[TD]Eldocis SA[/TD]
[TD]MC68HC11a1F/SMD[/TD]
[TD]SN74LS244J[/TD]
[/TR]
</tbody>[/TABLE]


Hope that helps!

 

[ellison]

*apologies, old product ID should read: MC68HC11A1F not MC68HC11A1FN

File 1 = Old_product ID
[TABLE="class: cms_table_cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Col A: old_product_ID[/TD]
[TD]Row B: Customers[/TD]
[/TR]
[TR]
[TD]MC68HC11A1F[/TD]
[TD]CRT Ltd[/TD]
[/TR]
[TR]
[TD]MC68HC11A1F[/TD]
[TD]Eldocis SA[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Put this code in the "old" workbook & with both books open try

Sub mergedata()
   Dim Ary1 As Variant, Ary2 As Variant, Oary As Variant
   Dim r As Long, i As Long, j As Long
   
   Ary1 = ThisWorkbook.Sheets("[COLOR=#ff0000]old[/COLOR]").Range("A1").CurrentRegion.value2
   Ary2 = Workbooks("[COLOR=#ff0000]abc.xlsx[/COLOR]").Sheets("[COLOR=#ff0000]New[/COLOR]").Range("A1").CurrentRegion.value2
   ReDim Oary(1 To UBound(Ary1) * UBound(Ary2), 1 To 4)
   For r = 2 To UBound(Ary1)
      i = i + 1
      Oary(i, 1) = Ary1(r, 1)
      Oary(i, 2) = Ary1(r, 2)
      For j = 2 To UBound(Ary2)
         If InStr(1, Ary2(j, 1), Oary(i, 1), 1) > 0 Then
            If Oary(i, 3) = Empty Then
               Oary(i, 3) = Ary2(j, 1)
               Oary(i, 4) = Ary2(j, 2)
            Else
               i = i + 1
               Oary(i, 1) = Ary1(r, 1)
               Oary(i, 2) = Ary1(r, 2)
               Oary(i, 3) = Ary2(j, 1)
               Oary(i, 4) = Ary2(j, 2)
            End If
         End If
      Next j
   Next r
   ThisWorkbook.Sheets("[COLOR=#ff0000]Sheet1[/COLOR]").Range("A2").Resize(i, 4).Value = Oary
End Sub

Change values in red to suit.
This will put the result in the "old" workbook

 

[ellison]

Wow, this is looking amazing! But I got an error message which reads "subscript out of range" - which is most probably my fault.....!

I've renamed files and sheets (which I was hoping would suit the code above but I think this is where I've messed up??!

Old Info:
Old.xlsm
Sheet is also called: old

New Info:
ABC.xlsx
sheet is called: new


I inserted the code only into Old.xlsm

As I said, I kept the red font above as it was....

"old" kept as "old"
"abc.xlsx" kept as "abc.xlsx"
"New" kept as "new"
"Sheet1" kept as Sheet1

When I hit AltF8 it brought up the Macro: "Sheet1.mergedata" which I ran on all Open Workbooks, but the error was "subscript out of range"




..... your help really is much appreciated!!!



PS If the above works out, then I'm thinking it may be something on the field headers that I've messed up...?

 

[Fluff]

If you click debug, what line of code is highlighted?

 

[ellison]

When I hit Alt F8, the options are to press "ok" or "help" when the "subscript out of range" error message pops up. "ok" seems to shut the pop up window and "help" seems to open an internet browser.

And when I view the code via hit debug... the options are:
step into: takes me the first line of code: sub mergedata ()
step over: highlights: "Ary1 = ThisWorkbook.Sheets("old").Range("A1").CurrentRegion.Value2"
...but if I click step over again, it highlights the next line Ary2... etc etc

The debugger doesn't seem to be highlighting a particular line of code, unless I'm mistaken?