VBA finding matching value in different column

[haxor]

Hi All

I need to match column T (20) value and see if it exists in column N where the column A value and the Column I values are equal. if it finds it, it needs to write the value: "502"
in column 7 where it found it

For example for row 7 and 8, column A and column I matches, so it needs to check the value in column T which is 8 in row 8. Column N in row 8 doesn't have the value but row 7 has.

The value for Row 7, column G needs to populate the value 502 because it found a matching value

The same for Row 11, 12 and 13 as their column A and column I matches, so it needs to check the value in column T which is 5 in row 13. Column N in row 13 doesn't have the value but row 12 has.

I have the following code (There is other code as well but I think this is the problem piece):

 If .Cells(counter, 1).Value = .Cells(counter + 1, 1).Value And .Cells(counter, 9).Value = .Cells(counter + 1, 9).Value Then
      
'.Cells(counter, 20).Value > 0 And
        
       Dim FoundCell As Range
       Dim ws As Worksheet
       Set ws = Sheets("Mini")
       Dim WHAT_TO_FIND As Integer

       WHAT_TO_FIND = .Cells(counter, 20).Value

       Set FoundCell = ws.Range("N:N").Find(What:=WHAT_TO_FIND)

       If Not FoundCell Is Nothing Then
                .Cells(counter, 7).Value = "502"
                MsgBox ("Found")
        Else
                .Cells(counter, 7).Value = "501"
                MsgBox ("Not Found")
                
        
            End If


   End If

 

[DanteAmor]

I need to match column T (20) value and see if it exists in column N where the column A value and the Column I values are equal. if it finds it, it needs to write the value: "502" in column 7 where it found it

Try this:

Sub finding_matching()
  Dim a As Variant, i As Long
  Dim dic As Object, s As String
  
  a = Range("A2:T" & Range("A" & Rows.Count).End(3).Row).Value2
  Set dic = CreateObject("Scripting.Dictionary")
  For i = 1 To UBound(a, 1)
    dic(a(i, 1) & "|" & a(i, 9) & "|" & a(i, 14)) = i
  Next i
  
  For i = 1 To UBound(a, 1)
    s = a(i, 1) & "|" & a(i, 9) & "|" & a(i, 20)
    If a(i, 20) <> "" Then If dic.exists(s) Then a(dic(s), 7) = "502"
  Next
  Range("G2").Resize(UBound(a)).Value = Application.Index(a, , 7)
End Sub

 

[haxor]

Hi Dante

I apologise for only responding now. Had internet issues

Thanks for you reply but it doesn't do anything. It doesn't put 502 where it matched

Do you know perhaps what could be the problem?

 

[Fluff]

Cross posted Match value in different Column and/or Row

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[haxor]

Good Day Fluff. I do apologise about this

If I do cross post in future I will state it

The other post details are:

Match value in different Column and/or Row

Good Afternoon All I need to match column T (20) value and see if it exists in column N where the column A value and the Column I values are equal. if it finds it, it needs to write the value: "502" in column 7 where it found it For example for row 7 and 8, column A and column I matches...

www.excelforum.com

Regards

 

[Fluff]

Thanks

 

[DanteAmor]

Do you know perhaps what could be the problem?

Maybe the test data does not have the same layout as the image presented in post # 1.

This is my test data:

varios 29jul2020.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Emp code	B	C	D	E	F		H	Date	J	K	L	M	Hours	O	P	Q	R	S	T
2	35	B2	C2	D2	E2	F2		H2	01-abr	J2	K2	L2	M2	9	O2	P2	Q2	R2	S2
3	35	B3	C3	D3	E3	F3		H3	02-abr	J3	K3	L3	M3	9	O3	P3	Q3	R3	S3
4	35	B4	C4	D4	E4	F4		H4	03-abr	J4	K4	L4	M4	9	O4	P4	Q4	R4	S4
5	35	B5	C5	D5	E5	F5		H5	04-abr	J5	K5	L5	M5	9	O5	P5	Q5	R5	S5
6	35	B6	C6	D6	E6	F6		H6	05-abr	J6	K6	L6	M6	9	O6	P6	Q6	R6	S6
7	104	B7	C7	D7	E7	F7		H7	06-abr	J7	K7	L7	M7	8	O7	P7	Q7	R7	S7
8	104	B8	C8	D8	E8	F8		H8	06-abr	J8	K8	L8	M8	9	O8	P8	Q8	R8	S8	8
9	208	B9	C9	D9	E9	F9		H9	08-abr	J9	K9	L9	M9	1	O9	P9	Q9	R9	S9
10	208	B10	C10	D10	E10	F10		H10	09-abr	J10	K10	L10	M10	9	O10	P10	Q10	R10	S10
11	283	B11	C11	D11	E11	F11		H11	06-abr	J11	K11	L11	M11	7	O11	P11	Q11	R11	S11
12	283	B12	C12	D12	E12	F12		H12	06-abr	J12	K12	L12	M12	5	O12	P12	Q12	R12	S12
13	283	B13	C13	D13	E13	F13		H13	06-abr	J13	K13	L13	M13	14	O13	P13	Q13	R13	S13	5
14	290	B14	C14	D14	E14	F14		H14	13-abr	J14	K14	L14	M14	9	O14	P14	Q14	R14	S14
15	422	B15	C15	D15	E15	F15		H15	30-mar	J15	K15	L15	M15	1	O15	P15	Q15	R15	S15
16	422	B16	C16	D16	E16	F16		H16	30-mar	J16	K16	L16	M16	7	O16	P16	Q16	R16	S16
17	422	B16	C16	D16	E16	F16		H16	30-mar	J16	K16	L16	M16	1	O16	P16	Q16	R16	S16	1
Hoja6

And this is the result:

varios 29jul2020.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Emp code	B	C	D	E	F		H	Date	J	K	L	M	Hours	O	P	Q	R	S	T
2	35	B2	C2	D2	E2	F2		H2	01-abr	J2	K2	L2	M2	9	O2	P2	Q2	R2	S2
3	35	B3	C3	D3	E3	F3		H3	02-abr	J3	K3	L3	M3	9	O3	P3	Q3	R3	S3
4	35	B4	C4	D4	E4	F4		H4	03-abr	J4	K4	L4	M4	9	O4	P4	Q4	R4	S4
5	35	B5	C5	D5	E5	F5		H5	04-abr	J5	K5	L5	M5	9	O5	P5	Q5	R5	S5
6	35	B6	C6	D6	E6	F6		H6	05-abr	J6	K6	L6	M6	9	O6	P6	Q6	R6	S6
7	104	B7	C7	D7	E7	F7	502	H7	06-abr	J7	K7	L7	M7	8	O7	P7	Q7	R7	S7
8	104	B8	C8	D8	E8	F8		H8	06-abr	J8	K8	L8	M8	9	O8	P8	Q8	R8	S8	8
9	208	B9	C9	D9	E9	F9		H9	08-abr	J9	K9	L9	M9	1	O9	P9	Q9	R9	S9
10	208	B10	C10	D10	E10	F10		H10	09-abr	J10	K10	L10	M10	9	O10	P10	Q10	R10	S10
11	283	B11	C11	D11	E11	F11		H11	06-abr	J11	K11	L11	M11	7	O11	P11	Q11	R11	S11
12	283	B12	C12	D12	E12	F12	502	H12	06-abr	J12	K12	L12	M12	5	O12	P12	Q12	R12	S12
13	283	B13	C13	D13	E13	F13		H13	06-abr	J13	K13	L13	M13	14	O13	P13	Q13	R13	S13	5
14	290	B14	C14	D14	E14	F14		H14	13-abr	J14	K14	L14	M14	9	O14	P14	Q14	R14	S14
15	422	B15	C15	D15	E15	F15		H15	30-mar	J15	K15	L15	M15	1	O15	P15	Q15	R15	S15
16	422	B16	C16	D16	E16	F16		H16	30-mar	J16	K16	L16	M16	7	O16	P16	Q16	R16	S16
17	422	B16	C16	D16	E16	F16	502	H16	30-mar	J16	K16	L16	M16	1	O16	P16	Q16	R16	S16	1
Hoja6

____________________________________
It would help if you can put your sample data using the XL2BB tool, download it from my signature.

____________________________________

 

[haxor]

Thank you Dante

Here it is using the Xl2BB from your signature:

Forum_Excel.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Emp Code	Surname	Name				JC Codes	Date	Date worked		JC Level2	JC Level1		Hours	Rate
2	35	GAN	ICE		1				01/04/2020	F	PLANTING	KONDI		9	23,32	9
3	35	GAN	ICE		2				02/04/2020	F	PLANTING	KONDI		9	23,32	18
4	35	GAN	ICE		3				03/04/2020	F	PLANTING	KONDI		9	23,32	27
5	35	GAN	ICE		4				30/03/2020	F	WEEDING	KONDI		9	23,32	36
6	35	GAN	ICE		5	15			31/03/2020	F	PICKING	KONDI		9	23,32	45
7	104	ANE	NWABISA		1				30/03/2020	F		KONDI		8	21,87	8
8	104	ANE	NWABISA		2	3			30/03/2020	F	Same date	KONDI		9	21,87	17	17			8
9	208	BAZ	N		1				30/03/2020	F	ANNUAL LEAVE	KONDI		1	23,32	1
10	208	BAZ	N		2	3			31/03/2020	F	ANNUAL LEAVE	KONDI		9	23,32	10
11	283	MENI	N		1				30/03/2020	F		SIPH		7	23,32	7
12	283	MENI	N		2				30/03/2020	F		SIPH		5	23,32	12
13	283	MENI	N		3	6			30/03/2020	F	Same date	SIPH		14	23,32	26	26			5
14	290	LIBI	N		1	1			30/03/2020	F	CANE SUPPORT	ALTUS		9	23,32	9
15	422	LAMBI	N		1				30/03/2020	F		SIPH		3	23,32	3
16	422	LAMBI	N		2	3			30/03/2020	F	Same date	SIPH		7	23,32	10	10			1
17	439	MEM	N		1				30/03/2020	F		SIPH		2	23,32	2
18	439	MEM	N		2				30/03/2020	F	Same date	SIPH		2	23,32	4	4
19	439	MEM	N		3	6			31/03/2020	F	SICK LEAVE	SIPH		9	23,32	13
Before

 

[DanteAmor]

With your data afeter run my macro:

varios 29jul2020.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Emp Code	Surname	Name				JC Codes	Date	Date worked		JC Level2	JC Level1		Hours	Rate
2	35	GAN	ICE		1				01/04/2020	F	PLANTING	KONDI		9	23.3	9
3	35	GAN	ICE		2				02/04/2020	F	PLANTING	KONDI		9	23.3	18
4	35	GAN	ICE		3				03/04/2020	F	PLANTING	KONDI		9	23.3	27
5	35	GAN	ICE		4				30/03/2020	F	WEEDING	KONDI		9	23.3	36
6	35	GAN	ICE		5	15			31/03/2020	F	PICKING	KONDI		9	23.3	45
7	104	ANE	NWABISA		1		502		30/03/2020	F		KONDI		8	21.9	8
8	104	ANE	NWABISA		2	3			30/03/2020	F	Same date	KONDI		9	21.9	17	17			8
9	208	BAZ	N		1				30/03/2020	F	ANNUAL LEAVE	KONDI		1	23.3	1
10	208	BAZ	N		2	3			31/03/2020	F	ANNUAL LEAVE	KONDI		9	23.3	10
11	283	MENI	N		1				30/03/2020	F		SIPH		7	23.3	7
12	283	MENI	N		2		502		30/03/2020	F		SIPH		5	23.3	12
13	283	MENI	N		3	6			30/03/2020	F	Same date	SIPH		14	23.3	26	26			5
14	290	LIBI	N		1	1			30/03/2020	F	CANE SUPPORT	ALTUS		9	23.3	9
15	422	LAMBI	N		1				30/03/2020	F		SIPH		3	23.3	3
16	422	LAMBI	N		2	3			30/03/2020	F	Same date	SIPH		7	23.3	10	10			1
17	439	MEM	N		1				30/03/2020	F		SIPH		2	23.3	2
18	439	MEM	N		2				30/03/2020	F	Same date	SIPH		2	23.3	4	4
19	439	MEM	N		3	6			31/03/2020	F	SICK LEAVE	SIPH		9	23.3	13
Hoja4

 

[haxor]

Thanks a lot Dante!

It works when I run it separately. There is something wrong with my other code that I need to fix

Regards