Partial Matching between two sheets

[zios007]

Hi Everyone! I'm new here and new to VBA. I'm trying to automate few things but I've spent lots of hours already looking on the internet, videos, forums, etc... and haven't been able to achieve anything, no success.

Here is what I'm trying to achieve, let see if I know how to explain it clearly.

- I have sheet1 with a list of descriptions
- I have sheet2 with a list of references
- The references in sheet2 can be found in the sheet1 list of descriptions but it is not an exact match since the descriptions have a lot of gibberish text in front and in the back of the reference.
- I need to do a partial match check from the reference in sheet2 with sheet1
- If the reference from sheet2 exists in any of the sheet1 descriptions (if it matches), then I would like to return columns b, c and d from sheet2 to columns in sheet1 f, h and i.
- Note: There may be times in sheet2 that one of the columns may not have data since it will get provided later. So if no data I would like to leave a blank field in sheet1.

I've been trying to test only to return c from sheet2 to h in sheet1 to start with, but not success.

These are my 2 sheets examples:

Sheet1:

Sheet2:

And here is a piece of code that I've been trying, but not results appear anywhere. If the code is crap, please change it as you prefer. I'm already too lost... maybe it is easier coding it differently.

Sub Find_ISL_REF()

 Dim rng2 As Range, c2 As Range, cfind As Range
    Dim x, y
    With Worksheets("Sheet1")
        Set rng2 = .Range(.Range("C2"), .Range("C2").End(xlDown))
        For Each c2 In rng2
            x = c2.Value
            With Worksheets("Sheet2").Columns("A:A")
                On Error Resume Next
                Set cfind = .Cells.Find(what:=x, lookat:=xlPart, LookIn:=xlValues)
                If (Not (cfind Is Nothing)) Then
                    y = cfind.Offset(0, 5).Value
                    c2.Offset(0, 5) = y
                End If
            End With
        Next c2
    End With

End Sub

I hope one of you brilliant and intelligent members can help me out. I'm sure it is much easier that I've been trying to code it for. But at this point, after hours of researching and testing, I come here for help.

Thank you very much in advance. I will appreciate any help.

Bests,
ZioS

 

[Alex Blakenburg]

Firstly you are doing a find for the longer string from Sheet 1 to the shorter string in Sheet 2, that is never going to work.
You need to reverse it and look for the shorter string (REF) inside the longer string (Line Descr)

Your reference code looks very consistent ie they start with NTRF and have a total length of 14 characters. Can this be relied on ?
Also your Line Descr looks to be quite structured. Does it always have the same number of slashes "/" in each line ?
and is each section between the slashes the exact same length ?
Have you considered splitting the Line Descr into its components ? and can we add a column for REF in Sheet1 ?

Looping through Sheet 2 and looking up each reference multiple times in Sheet 1 could be quite a slow process.
What sort of volume are we talking about ie no of rows in each of Sheet1 & 2.

Can you provide an XL2BB extract of both Sheet 1 & 2.

XL2BB
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[zios007]

Firstly you are doing a find for the longer string from Sheet 1 to the shorter string in Sheet 2, that is never going to work.
You need to reverse it and look for the shorter string (REF) inside the longer string (Line Descr)

Your reference code looks very consistent ie they start with NTRF and have a total length of 14 characters. Can this be relied on ?
Also your Line Descr looks to be quite structured. Does it always have the same number of slashes "/" in each line ?
and is each section between the slashes the exact same length ?
Have you considered splitting the Line Descr into its components ? and can we add a column for REF in Sheet1 ?

Looping through Sheet 2 and looking up each reference multiple times in Sheet 1 could be quite a slow process.
What sort of volume are we talking about ie no of rows in each of Sheet1 & 2.

Can you provide an XL2BB extract of both Sheet 1 & 2.

XL2BB
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Dear Alex, I'm sorry for the delay. I'm on an international trip. Will get back tomorrow. I will try to give you the information that you are asking me between tomorrow or Wednesday. Thank you for your patience and willingness to help. I really appreciate it.

 

[zios007]

Firstly you are doing a find for the longer string from Sheet 1 to the shorter string in Sheet 2, that is never going to work.
You need to reverse it and look for the shorter string (REF) inside the longer string (Line Descr)

Your reference code looks very consistent ie they start with NTRF and have a total length of 14 characters. Can this be relied on ?
Also your Line Descr looks to be quite structured. Does it always have the same number of slashes "/" in each line ?
and is each section between the slashes the exact same length ?
Have you considered splitting the Line Descr into its components ? and can we add a column for REF in Sheet1 ?

Looping through Sheet 2 and looking up each reference multiple times in Sheet 1 could be quite a slow process.
What sort of volume are we talking about ie no of rows in each of Sheet1 & 2.

Can you provide an XL2BB extract of both Sheet 1 & 2.

XL2BB
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

- Firstly you are doing a find for the longer string from Sheet 1 to the shorter string in Sheet 2, that is never going to work.
You need to reverse it and look for the shorter string (REF) inside the longer string (Line Descr)
----> I've switch sheet1 for sheet2 and vice versa.

Sub Find_ISL_REF()

 Dim rng2 As Range, c2 As Range, cfind As Range
    Dim x, y
    With Worksheets("Sheet2")
        Set rng2 = .Range(.Range("C2"), .Range("C2").End(xlDown))
        For Each c2 In rng2
            x = c2.Value
            With Worksheets("Sheet1").Columns("A:A")
                On Error Resume Next
                Set cfind = .Cells.Find(what:=x, lookat:=xlPart, LookIn:=xlValues)
                If (Not (cfind Is Nothing)) Then
                    y = cfind.Offset(0, 5).Value
                    c2.Offset(0, 5) = y
                End If
            End With
        Next c2
    End With

End Sub

- Your reference code looks very consistent ie they start with NTRF and have a total length of 14 characters. Can this be relied on ?
----> No it cannot because depending on the country the description will come differently, different order and different way of referencing. I've added some more data. You will see that for another country there is a COBR SEPA and the numbers instead of the NTRF#
- Also your Line Descr looks to be quite structured. Does it always have the same number of slashes "/" in each line ?
and is each section between the slashes the exact same length ?
----> As said above, not, it will vary depending the country.

Have you considered splitting the Line Descr into its components ? and can we add a column for REF in Sheet1 ?
----> I will have different sheets with the reference codes for each country. I still need to identify these codes because it varies. This is descriptions that comes from the bank and each bank and country manages it differently (I still figuring out how to identify the references if any). That is why I create a table of those that I've identified and can be consistent each month.

Book1.xlsx
	A	B	C	D	E	F	G	H	I
1	Doc Date	Tran Ref	Line Descr	Amount	Dept ID	Account	Product	Project	Line Description
2	03/01/2022	02582309	RENT/BENM/HILMAR AEVAR HILMA-:61:2201010103D216313NTRF1202582309//RENT:86:RENT/BENM/HILMAR AEVAR HILMARSSON	-7340,00
3	03/01/2022	02582309	/BENM/DALTUN EHF. -:61:2201010103D232701NTRF051566000006//0010122:86:/BENM/DALTUN EHF.	-7340,00
4	03/01/2022	02582309	/BENM/HEIMSTADEN EHF. -:61:2201010103D204273NTRF4403151190//0010122:86:/BENM/HEIMSTADEN EHF.	-7340,00
5	03/01/2022	02582309	/BENM/HEIMSTADEN EHF. -:61:2201010103D279612NTRF4403151190//0010122:86:/BENM/HEIMSTADEN EHF.	-7340,00
6	03/01/2022	02582309	/BENM/STEINAGERDI EHF. -:61:2201010103D270970NTRF7001972989//0010122:86:/BENM/STEINAGERDI EHF.	-7340,00
7	03/01/2022	02582309	RENT/BENM/ANNA HARSNYRTISTOF-:61:2201010103D120000NTRF4804090350//RENT:86:RENT/BENM/ANNA HARSNYRTISTOFA EHF.	-7340,00
8	03/01/2022	02582309	RENT/BENM/GUNNAR JOHANNESSON-:61:2201010103D205000NTRF2805773169//RENT:86:RENT/BENM/GUNNAR JOHANNESSON	-7340,00
9	03/01/2022	02582309	RENT/BENM/BJORN TOR GUDMUNDS-:61:2201010103D210000NTRF1602982789//RENT:86:RENT/BENM/BJORN TOR GUDMUNDSSON	-7340,00
10	03/01/2022	02582309	GARAGE/BENM/HILMAR AEVAR HIL-:61:2201010103D32987NTRF1202582309//GARAGE:86:GARAGE/BENM/HILMAR AEVAR HILMARSSON	-7340,00
11	03/01/2022	02582309	/BENM/HRINGDU EHF. -:61:2201010103D29179NTRF6403090670//0071221:86:/BENM/HRINGDU EHF.	-7340,00
12	03/01/2022	02582309	/BENM/NOVA HF. -:61:2201010103D42137NTRF5312050810//0281221:86:/BENM/NOVA HF.	-7340,00
13	03/01/2022	02582309	/BENM/REYKJAVIKURBORG -:61:2201010103D11533NTRF5302697609//0041221:86:/BENM/REYKJAVIKURBORG	-7340,00
14	03/01/2022	02582309	/BENM/VEITUR OHF. -:61:2201010103D18836NTRF5012131870//0050122:86:/BENM/VEITUR OHF.	-7340,00
15	03/01/2022	02582309	/BENM/ORYGGISMIDSTOD ISLANDS H-:61:2201110111D3993NTRF4109953369//0060122:86:/BENM/ORYGGISMIDSTOD ISLANDS HF.	-7340,00
16	03/01/2022	02582309	-:61:2201110111D955NTRFC2A11ZPWKG483CIM//C2A11ZPWKG483CIM	-7340,00
17	03/01/2022	02582309	/BENM/TENGIR HF. -:61:2201110111D3508NTRF6607022880//0010122:86:/BENM/TENGIR HF.	-7340,00
18	03/01/2022	02582309	/BENM/SJOVA-ALMENNAR TRYGGINGA-:61:2201170117D29189NTRF6509091270//0201221:86:/BENM/SJOVA-ALMENNAR TRYGGINGAR	-7340,00
19	03/01/2022	02582309	/BENM/HS VEITUR HF. -:61:2201190119D6523NTRF4312080590//0311221:86:/BENM/HS VEITUR HF.	-7340,00
20	03/01/2022	02582309	JACOB CHARLES RASMUSSEN 15.10.-:61:2201190119D15000NTRF6702696399//C2A19ZPMYD1RH6GL:86:JACOB CHARLES RASMUSSEN 15.10.2002/BENM/UTLENDINGASTOFNUN	-7340,00
21	03/01/2022	02582309	BRENT RAY BARRETT 14.04.1958-:61:2201190119D15000NTRF6702696399//C2A19ZPNPG7RHAL5:86:BRENT RAY BARRETT 14.04.1958/BENM/UTLENDINGASTOFNUN	-7340,00
22	03/01/2022	02582309	GINGER ANN BARRETT 25.06.1959-:61:2201190119D15000NTRF6702696399//C2A19ZPOLD2RHF7H:86:GINGER ANN BARRETT 25.06.1959/BENM/UTLENDINGASTOFNUN	-7340,00
23	03/01/2022	02582309	/BENM/SELFOSSVEITUR BS -:61:2201190119D10858NTRF6309922069//0010122:86:/BENM/SELFOSSVEITUR BS	-7340,00
24	03/01/2022	02582309	/BENM/HS VEITUR HF. -:61:2201190119D3395NTRF4312080590//0311221:86:/BENM/HS VEITUR HF.	-7340,00
25	03/01/2022	02582309	DISH WASHER INSTALLING/BENM/-:61:2201250126D27094NTRF7001972989//PLUMBER:86:DISH WASHER INSTALLING/BENM/STEINAGERDI EHF.	-7340,00
26	03/01/2022	02582309	/BENM/SIMINN HF. -:61:2201290131D7340NTRF4602070880//0200122:86:/BENM/SIMINN HF.	-7340,00
27	03/01/2022	02582309	/BENM/DALTUN EHF. -:61:2201310201D233744NTRF051566000006//0010222:86:/BENM/DALTUN EHF.	-7340,00
28	03/01/2022	02582309	/BENM/HEIMSTADEN EHF. -:61:2201310201D198245NTRF4403151190//0010222:86:/BENM/HEIMSTADEN EHF.	-7340,00
29	03/01/2022	02582309	/BENM/HEIMSTADEN EHF. -:61:2201310201D280864NTRF4403151190//0010222:86:/BENM/HEIMSTADEN EHF.	-7340,00
30	03/01/2022	02582309	/BENM/STEINAGERDI EHF. -:61:2201310201D272185NTRF7001972989//0010222:86:/BENM/STEINAGERDI EHF.	-7340,00
31	03/01/2022	02582309	RENT/BENM/GUNNAR JOHANNESSON-:61:2201310201D205000NTRF2805773169//RENT:86:RENT/BENM/GUNNAR JOHANNESSON	-7340,00
32	03/01/2022	02582309	RENT/BENM/HILMAR AEVAR HILMA-:61:2201310201D216313NTRF1202582309//RENT:86:RENT/BENM/HILMAR AEVAR HILMARSSON	-7340,00
33	03/01/2022	02582309	RENT/BENM/BJORN TOR GUDMUNDS-:61:2201310201D180000NTRF1602982789//RENT:86:RENT/BENM/BJORN TOR GUDMUNDSSON	-7340,00
34	03/01/2022	02582309	RENT/BENM/REYNIR VIDAR GEORG-:61:2201310201D200000NTRF2101543029//RENT:86:RENT/BENM/REYNIR VIDAR GEORGSSON	-7340,00
35	03/01/2022	02582309	GARAGE/BENM/HILMAR AEVAR HIL-:61:2201310201D32987NTRF1202582309//GARAGE:86:GARAGE/BENM/HILMAR AEVAR HILMARSSON	-7340,00
36	03/01/2022	02582309	/BENM/HRINGDU EHF. -:61:2201310201D30169NTRF6403090670//0070122:86:/BENM/HRINGDU EHF.	-7340,00
37	03/01/2022	02582309	/BENM/NOVA HF. -:61:2201310201D43491NTRF5312050810//0260122:86:/BENM/NOVA HF.	-7340,00
38	03/01/2022	02582309	COBR SEPA 00172526255 AGUAS DO- 61 2205160516D3485NDDTCOBR SEPA 001725//0516S12847036831 86 COBR SEPA 00172526255 AGUAS DO SADO	-7340,00
39	03/01/2022	02582309	COBR SEPA 96258833686 CTT - AG- 61 2205160516D3NDDTCOBR SEPA 962588//0516S12847064706 86 COBR SEPA 96258833686 CTT - AGUA/GAS	-7340,00
40	03/01/2022	02582309	COBR SEPA 00002878963 AGERE E- 61 2205160516D2058NDDTCOBR SEPA 000028//0516S12847015048 86 COBR SEPA 00002878963 AGERE E.M.	-7340,00
41	03/01/2022	02582309	13/05 PAG. PORTAGEM/TELEF. PUB- 61 2205160516D222NMSC13/05 PAG. PORTA//03116888 86 13/05 PAG. PORTAGEM/TELEF. PUBL. EL-E 3031168/88	-7340,00
42	03/01/2022	02582309	COBR SEPA 97013628722 Servicos- 61 2205160516D449NDDTCOBR SEPA 970136//0516S12847035839 86 COBR SEPA 97013628722 Servicos Municipalizados de Agua e	-7340,00
43	03/01/2022	02582309	COBR SEPA 00294387915 SIMAR - 61 2205160516D4954NDDTCOBR SEPA 002943//0516S12847069193 86 COBR SEPA 00294387915 SIMAR	-7340,00
44	03/01/2022	02582309	14/05 PAG. PORTAGEM/TELEF. PUB- 61 2205160516D5815NMSC14/05 PAG. PORTA//03116889 86 14/05 PAG. PORTAGEM/TELEF. PUBL. EL-E 3031168/89	-7340,00
45	03/01/2022	02582309	COBR SEPA 00148562120 Servicos- 61 2205160516D6472NDDTCOBR SEPA 001485//0516S12847035316 86 COBR SEPA 00148562120 Servicos Municipalizados de Agua e	-7340,00
46	03/01/2022	02582309	COBR SEPA 00000187991 GALP Pow- 61 2205160516D7918NDDTCOBR SEPA 000001//0516S12847039162 86 COBR SEPA 00000187991 GALP Power	-7340,00
47	03/01/2022	02582309	COBR SEPA 00000344111 GALP Pow- 61 2205160516D1126NDDTCOBR SEPA 000003//0516S12847039319 86 COBR SEPA 00000344111 GALP Power	-7340,00
48	03/01/2022	02582309	COBR SEPA 00000187982 GALP Pow- 61 2205160516D11852NDDTCOBR SEPA 000001//0516S12847039402 86 COBR SEPA 00000187982 GALP Power	-7340,00
49	03/01/2022	02582309	COBR SEPA 00000197742 GALP Pow- 61 2205160516D20908NDDTCOBR SEPA 000001//0516S12847037402 86 COBR SEPA 00000197742 GALP Power	-7340,00
50	03/01/2022	02582309	COBR SEPA 00000187780 GALP Pow- 61 2205160516D34943NDDTCOBR SEPA 000001//0516S12847039352 86 COBR SEPA 00000187780 GALP Power	-7340,00
51	03/01/2022	02582309	COBR SEPA 68103945382 EMAS Emp- 61 2205170517D8162NDDTCOBR SEPA 681039//0517S13155005331 86 COBR SEPA 68103945382 EMAS Empresa Municipal de agua e	-7340,00
52	03/01/2022	02582309	COBR SEPA 17000836648 Jet Cool- 61 2205170517D945NDDTCOBR SEPA 170008//0517S13155020387 86 COBR SEPA 17000836648 Jet Cooler Aguas e Cafes S A	-7340,00
53	03/01/2022	02582309	COBR SEPA 00168187063 Servicos- 61 2205170517D1452NDDTCOBR SEPA 001681//0517S13155011286 86 COBR SEPA 00168187063 Servicos Municipalizados de Agua e	-7340,00
54	03/01/2022	02582309	COBR SEPA 00004380904 SMAS Cas- 61 2205170517D53NDDTCOBR SEPA 000043//0517S13155005614 86 COBR SEPA 00004380904 SMAS Castelo Branco	-7340,00
55	03/01/2022	02582309	COBR SEPA 00000197740 GALP Pow- 61 2205170517D20625NDDTCOBR SEPA 000001//0517S13155012691 86 COBR SEPA 00000197740 GALP Power	-7340,00
Sheet1

Book1.xlsx
	A	B	C	D
1	REF	Account	Project	Description
2	NTRF0515660000	5862	MSH1460-0115	Electricity
3	NTRF1202582309	5862	MSH1460-0183	Gas
4	NTRF1602982789	5862	MSH1460-0180	Water
5	NTRF2101543029	5862	MSH1460-0181	Electricity
6	NTRF2108552119	5102		Gas
7	NTRF2805773169	5862	MSH1460-0208	Water
8	NTRF4109953369			Electricity
9	NTRF4312080590	5868	MSH1460-0181	Gas
10	NTRF4403151190	5862	MSH1460-0212	Water
11	NTRF4602070880	5370		Electricity
12	NTRF4711190830			Gas
13	NTRF4712161190	5868	MSH1460-0219	Water
14	NTRF4804090350	5862	MSH1460-0181	Electricity
15	NTRF5012131870			Gas
16	NTRF5202692669	5868	MSH1460-0181	Water
17	NTRF5302697609	5862	MSH1460-0075	Electricity
18	NTRF5312050810	5370		Gas
19	NTRF5912130160	5868	MSH1460-0075	Water
20	NTRF6302110500	5772		Electricity
21	NTRF6309922069	5868	MSH1460-0181	Gas
22	NTRF6403090670	5370		Water
23	NTRF6509091270	5772		Electricity
24	NTRF6607022880	5370		Gas
25	NTRF6702696399	5102		Water
26	NTRF6811023020	5480		Electricity
27	NTRF7001972989	5862	MSH1460-0211	Gas
28	NTRFC2A11ZPWKG			Water
29	NTRFNONREF//C1			Electricity
30	NTRF7003110310			Gas
31	NTRF0811542799			Water
32	NTRF5011700119			Electricity
Sheet2

Book1.xlsx
	A	B	C	D
1	REF	Account	Project	Description
2	COBR SEPA 282799-1 SEC	5868 Utilities	MO-Alarm	Alarm
3	COBR SEPA 05134636327	5868 Utilities	MO-Wifi	Wifi
4	COBR SEPA 00000187582	5868 Utilities	MSH1800-0092	Electricity
5	COBR SEPA 00620805325	5868 Utilities	MSH1800-0092	Gas
6	COBR SEPA 00000186703	5868 Utilities	MSH1800-0095	Water
7	COBR SEPA 95400672843	5868 Utilities	MSH1800-0095	Electricity
8	COBR SEPA MND100000629	5868 Utilities	MSH1800-0109	Gas
9	COBR SEPA 00000197726	5868 Utilities	MSH1800-0180	Water
10	COBR SEPA 00148563478	5868 Utilities	MSH1800-0180	Electricity
11	COBR SEPA 00000187681	5868 Utilities	MSH1800-0181	Gas
12	COBR SEPA 68103923343	5868 Utilities	MSH1800-0181	Water
13	COBR SEPA 00000197740	5868 Utilities	MSH1800-0191	Electricity
14	COBR SEPA 00294387915	5868 Utilities	MSH1800-0191	Gas
15	COBR SEPA 00000187581	5868 Utilities	MSH1800-0200	Water
16	COBR SEPA 95953932333	5868 Utilities	MSH1800-0200	Electricity
17	COBR SEPA 00000187588	5868 Utilities	MSH1800-0231	Gas
18	COBR SEPA 01191711726	5868 Utilities	MSH1800-0231	Water
19	COBR SEPA 00000187585	5868 Utilities	MSH1800-0233	Electricity
20	COBR SEPA 96258833686	5868 Utilities	MSH1800-0233	Gas
21	COBR SEPA 00000187584	5868 Utilities	MSH1800-0234	Water
22	COBR SEPA 96779139172	5868 Utilities	MSH1800-0234	Electricity
23	COBR SEPA 00000188086	5868 Utilities	MSH1800-0238	Gas
24	COBR SEPA 95530423438	5868 Utilities	MSH1800-0238	Water
25	COBR SEPA 00000197188	5868 Utilities	MSH1800-0247	Electricity
26	COBR SEPA 00000187593	5868 Utilities	MSH1800-0247	Gas
27	COBR SEPA 95713565945	5868 Utilities	MSH1800-0247	Water
28	COBR SEPA MND100000008	5868 Utilities	MSH1800-0249	Electricity
29	COBR SEPA 00077103389	5868 Utilities	MSH1800-0249	Gas
30	COBR SEPA 00002822378	5868 Utilities	MSH1800-0249	Water
31	COBR SEPA 00000187587	5868 Utilities	MSH1800-0269	Electricity
32	COBR SEPA 01201282619	5868 Utilities	MSH1800-0269	Gas
Sheet3

Looping through Sheet 2 and looking up each reference multiple times in Sheet 1 could be quite a slow process.
What sort of volume are we talking about ie no of rows in each of Sheet1 & 2.
----> It will not be much. Being slow isn't a big issue (unless it is really too slow). Each file will not have more than 200 entries I would say. It depends on the country, but maybe the largest will be about 300 once in a while? But usually around 50 lines?

Let me know if attaching the file will be easier.

So my goal here with the VBA code is:
1- check the table in Sheet2 or Sheet3 or SheetX depending on the country I will be running it for, read the reference. If it cannot be added in 1 VBA code I can run the code for the different countries manually. Let say I put data from PRT, then I will run the code for PRT which may be sheet3.
2- if the reference, for example in Sheet2, matches within the Line Description in Sheet1
3- then write in sheet1 on its corresponding column the data found the matched reference in sheet2 (account, project and description)
4- later I will try to figure out how to determine from the project in sheet1 which DeptID to put in the sheet1 column with another table for DeptIDs, lets call it sheetY (but this will come later)
Cause this table will look like Project: MSH1800-0142 DeptID: 1800400, pretty straight forward. Project will almost always have 12 characters with really few exceptions (3 leading letters, 4 digits, slash, 4 digits). And the DeptID 7 numerical digits.

Book1.xlsx
	A	B
1	Project	Dept
2	MO-Alarm	1800000
3	MO-Wifi	1800000
4	MSH1460-0058	1460400
5	MSH1460-0068	1460480
6	MSH1460-0075	1460402
7	MSH1460-0080	1460000
8	MSH1460-0089	1460400
9	MSH1460-0099	1460400
10	MSH1460-0115	1460402
11	MSH1460-0118	1460000
12	MSH1460-0132	1460400
13	MSH1460-0135	1460400
14	MSH1460-0143	1460400
15	MSH1460-0147	1460400
16	MSH1460-0169	1460400
17	MSH1460-0178	1460400
18	MSH1460-0180	1460402
19	MSH1460-0181	1460402
20	MSH1460-0183	1460482
21	MSH1460-0208	1460402
22	MSH1460-0210	1460400
23	MSH1460-0211	1460482
24	MSH1460-0212	1460482
25	MSH1460-0219	1460402
26	MSH1800-0092	1800400
27	MSH1800-0095	1800400
28	MSH1800-0109	1800400
29	MSH1800-0111	1800400
30	MSH1800-0159	1800400
31	MSH1800-0235	1800400
32	MSH1800-0238	1800400
33	MSH1800-0239	1800400
34	MSH1800-0247	1800400
35	MSH1800-0249	1800400
36	MSH1800-0262	1800400
37	MSH1800-0267	1800400
38	MSH1800-0269	1800400
39	MSH-M14547	1800400
Sheet5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A39	Cell Value	duplicates	text	NO

I really hope it is easy. But as I said, I've been reading, and reading, and reading and testing many different things for hours, but I've not been able to be successful due to my low VBA/Excel knowledge.

Once again, thank you for the effort and the help. It is much appreciated.

 

[zios007]

Oh, with the piece of code I was just testing with the project code. If the ref in sheet2 matches with any text in sheet1, then copy and paste the project code in sheet1. Just to start with something. So definitely it is half way of what I'm trying to do. But since that didn't work, I didn't add anything else to it.

 

[Alex Blakenburg]

It is quite late here and I am about to log off.
So does it work for you if I just start with getting Sheet2 to update Sheet1 based on finding the Sheet2 code whever it appears in Sheet1 ?
If so I will look at doing that tomorrow.

 

[zios007]

It is quite late here and I am about to log off.
So does it work for you if I just start with getting Sheet2 to update Sheet1 based on finding the Sheet2 code whever it appears in Sheet1 ?
If so I will look at doing that tomorrow.

Hey! no rush Alex. I really appreciate your willingness to help me (I wished I could do the same for others.. maybe with time :P). It is not something urgent. So it can wait. Thank you very much. Have a good night!

 

[zios007]

Yesterday spending some hours on it, I found a formula string that does the job. Now, I have no idea how to put this on VBA and of course a loop and a variable that increments on each loop will need to happen to go to the next lines.

I've created 1 table (it worked also with range references, also included below), but with the table was easier to work for the other columns in sheet2. The table in sheet2 was done from column A to D (named: Tablesheet2Ref).

I've also combined sheet2 and sheet3. That way I do not have to deal with countries separately. The formula checks all unique reference codes in sheet2 and determines if it's found in sheet1 and what will be copied to the specified column in sheet1
I've also created 2 other sheets. Sheet3 which will contain all the unique DeptIDs and Sheet4 which will contain all the unique Accounts.

sheet3 example

sheet4 example

Formula with table for the Project in sheet2 column:
=IFNA(LOOKUP(2;1/SEARCH(Tablesheet2Ref[Ref];'sheet1'!C2);Tablesheet2Ref[Project]);"")

For the account column:
=IFNA(LOOKUP(2;1/SEARCH(Tablesheet2Ref[Ref];'sheet1'!C2);Tablesheet2Ref[Account]);"")

For the DeptID column:
=IFNA(LOOKUP(2;1/SEARCH(Tablesheet3DeptID[Project];'sheet1'!I2);Tablesheet3DeptID[Dept]);"")

For the Description column:
=IFNA(LOOKUP(2;1/SEARCH(Tablesheet4Accounts[Description Chart of Accounts];'sheet1'!G2);Tablesheet4Accounts[Description]);"")



Formula with range cells
=IFNA(LOOKUP(2;1/SEARCH('sheet2'!$A$2:$A$228;'sheet1'!C2);'sheet2'!$C$2:$C$228);"")
And same methodology for the other columns. But I think with the tables was easier to work with.

Now... how to put this in VBA code? No idea... "yet".

 

[Alex Blakenburg]

I was about to have a look, so good timing. How did that perform on your full data set ? Did it significantly slow down your Excel or was the performance fine.

 

[zios007]

I was about to have a look, so good timing. How did that perform on your full data set ? Did it significantly slow down your Excel or was the performance fine.

Nah, it was fine. It runs pretty quick. Pretty much instantly.

Now it is just a matter of plugging that into VBA code instead of running the formulas on each cell.