Partial Matching and sorting (cant get it right) PLEASE HELP

Hi Guys

This is my first post but been looking all over the internet for something that can help and hopefully get this to work. \

This is the best method i can think to explain this.

Combined Branch 1 combined code and description( =A3&"|"&B3 )

001-19114U|19X114 SAP RGH UNTR
001-2511418|25X114 SAP IND TRD 1.80M PER M
001-2511421|25X114 SAP IND TRD 2.10M PER M
001-2511424|25X114 SAP IND TRD 2.40M PER M
001-2511430|25X114 SAP IND TRD 3.00M PER M
001-2511433|25X114 SAP IND TRD 3.30M PER M
001-2511436|25X114 SAP IND TRD 3.60 PER MT
001-2511439|25X114 SAP IND TRD 3.90M PER M
001-2511442|25X114 SAP IND TRD 4.20M PER M
001-2511445|25X114 SAP IND TRD 4.50M PER M
001-2511448|25X114 SAP IND TRD 4.80 PER MT
001-25114IND|25X114 SAP IND U/T SHORTS P/MT
001-25114IND L|25X114 SAP IND U/T LONGS PER M

All other branches must work off this list (above)

Combined Branch 2 (=C3&"|"&D3)

001-25114 IND.|25X114 SAP IND. UNTRD.
001-2511418|25X114 SAP IND TRD 1.8M
001-2511421|25X114 SAP IND TRD 2.1M
001-2511424|25X114 SAP IND TRD
001-2511430|25X114 SAP IND TRD 3.0M
001-2511433|25X114 SAP IND TRD 3.3M
001-2511442|25X114 IND TRD SAP 4.2M
001-2511448|25X114 SAP IND RTD 4.8M
001-25114IND|25X114 SAP IND TRD 3.6M
001-2515230|25X152 SAP IND TRD 3.00
001-2515236|25X152 SAP IND TRD 3.60
001-2515242|25X152 SAP IND TRD 4.20
001-2515245|25X152 SAP IND TRD 4.5

trying to get partial matches between branch 1 and branch 2 that sort to the same row but different column as branch 1, there is close to 12500 codes for products. so the list is long and most codes and descriptions are similar.

Please Help
Thank you kindly

Sorry forgot to mention currently i am using this formula to sort and order most of it but its only for exact matches and cant seem to figure out how to change it to partial match.
=IF(ISNA(MATCH(H3;$I$3:$I$12500;0));"";INDEX($I$3:$I$12500;MATCH(H3;$I$3:$I$12500;0)))

hope this helps
thanks

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hi sandy

Thanks for the reply i attached a sample file of the main file i am working on hope this helps to explain my thinking better

Kindest Regards
Scott

what you want to compare? Item Code or Description in both combined results?

Hi sandy

there are in total 3 branches that sell the same stuff but they are currently on different accounting systems. i need to use the sub branches(2) stock, and compare it to the main branch stock, and alter the item code and description to match the main branch while keeping a record of what i have changed.

for instance the main branch has a code for a stock item lets call it "apple" and the code is "01" so it would look like this.
"01|apple".
now the one sub branch has apple as well with the same code but slightly different description.
"01|red apple"
now the other branch also has apple but slightly different code.
"0-1|apple"

The reason i need it to sort is say item "apple" for main branch is in row 1, item "apple" for sub branch 2 is in row 200. ect ect. if i can get help to get excel to find partial matches in different columns and arrange them in row 1- 10 for example so i can edit them that would be awesome

they are all the same stock item just different people put different descriptions on stock items in the 3 systems. and currently i need to go through it all to sort out so we can put the 3 branches on 1 system.

Hope this clears it up a little better

Thanks sandy

Hi sandy
Below is an example of 2 products with similar description and codes but not exactly the same and they also dont appear in the same row.

not sure if possible to get that organised as how i have imagined it...

"02-449424|44X94 H/WOOD PAR 2.4M U/T /MT" "02-449424|44X94 PAR H/WOOD 2.4M"

"01-COLSK8229430|SKIRTING COLSK8 22X94X3.0M/MT" "01-COLSK8|SKIRTING COLSK8 22X94 3M"

I have been thinking if it can be done, where it can be worked of the item code as most of them are the same not all but at least about 80% of the 12500 long list has the same item code.
so we take item code in column "A" skip the description in Column "B" and get Column "C" item code to organise/sort to match column "A" and pull Column "D" description with it.

that way it should all be in line, well most of it then its still a lot of manual but at least at that point workable.

any thoughts on that approach ?

Thanks you are awesome for been such a big help.

Kindest Regards
Scott

Hi sandy

that probably would not work due to there been to many "items" and would have to do that per "item". not sure how one would go about automating that process ?.

probably not a real possibility due to the codes not been the same length "eg. 01-001| 001-001| 001-1001100". if they were the same length and i combined it with the description in a certain order it might work as could get excel to read "right" say 10 spaces and use that to bring about all similar codes. wish i was here when they were making the codes could have standardized all of this nightmare.

so far it seems like i have to do it all manually

if you have any other ideas i would love to hear them.

Kindest Regards
Scott

I don't know yet but I can try
but first: why you concatenating then compare?
second: if it is not sensitive, could you attach more data with description which one is correct and which one isn't correct and why, say 100 rows ?
I'll try to find a pattern

Hi Sandy

i am going to send you the entire list all the raw data. i have split it into a few worksheets as its a complete mess on this sample there is no formula or anything just raw data.

basically all three branches have to match product code and description and where they don't match it needs to highlight. so can be found and changed. don't worry about the other worksheets just "MAIN"

the reason i used concatenating (probably a hair brain scheme) but i thought it would be easier to try and get matches between the 3 branches and after that i would find away to split it again into item code and description.

the 2 sub branches needs to match the main branch and if not highlight what does not match and then it also needs to add the other way around as the sub branches also have items the main branch does not so that needs to be added into the main branch.

If you have an idea i would love to implement it as it would make the process so much quicker.

Thanks again for all the HELP

Originally Posted by sandy666

which one is correct and which one isn't correct and why?

You didn't say that

I need some direction because now all are correct (or not) because much of them starting with, eg.: 1

H Sandy

The Main Branch is suppose to be the correct one so the other 2 will match there data with the main branch, but as you might see in what i sent not all codes have the same item between the 3 branches which is frustrating. if it could match to all the ones that are the same code and a partial match on the description would solve months of effort.

Not sure if this clears up the your question.

Thanks again

example
(this is copied from Main)
03-0002^DOOR PINE BATTEN STABLE
03-0003^DOOR F/CHECK CLASSIQUE 813

these two are the same in Main and second branch but in another rows. There are much more like this
Maybe you need remove all duplicates from Main and Second branch (temporary) to find all what you need to correct. Less work
The same between Main and third branch

My question was because only you know what is correct or not

So far, I do not see a universal solution

ahhhhhhh

Honestly i am not even 100% sure on all the stock items.

but those 2 are different items that must try and match with items in branch 2 and 3 after getting a match they must be highlighted say green for "correct" and no matches should highlight say red for "incorrect", and yes not everything is on the same row. Currently i am working it as everything in say code "003" goes into one work sheet and i try sort it from there but still a nightmare and they mostly don't line up so a lot of playing with it and i get there.

but you are right i cant see a universal solution to this problem ether not even sure if there would be one. as i am sure microsoft tried to make it as idiot proof as possible but nobody thought that people could screw things up this badly hahahaha.

Even with your very professional help and vast knowledge i doubt it could work, even though i would love it if something came right.

Kindest Regards

But I'm not giving up (yet!)

I need time, hope it is not for "yesterday"

who puts the asterisks at the end of the text????????????????????

Check test 1 & test 2
maybe now will be easier to correct