Need code which analyze the data b/w two sheets and if the same value is found in col A in 2 sheets then it needs to check adjacent cell

[Sunil113]

Hi,

Need code which performs the below operations.
Info :
I have two sheets with data in an excel. The data in both the sheets will be in same format.
Sheet 1 will be having header.
Sheet 2 will be without header.
Operations to be performed:
1. Value in sheet 1 Cell A2 needs to be checked with value in Sheet 2 entire column A.
2. If the value is matching then it needs to check the adjacent cell value (column B) in both the sheets.
3. If the value in sheet 2 is greater than the value in sheet 1 then the entire row (from column B to column Z) needs to be copy paste to sheet 1.

Thank you

 

[mumps]

Where in Sheet1 do you want to paste the entire row?

 

[Sunil113]

Where in Sheet1 do you want to paste the entire row?

Please find the attached images for better understanding.
In the attached excel, I have data in 2 sheets, Sheet 1 with headers & sheet 2 without headers. In sheet 1 no.of columns is more compared to sheet 2
1. Value in sheet 1 Cell A2 needs to be checked with value in Sheet 2 entire column A. __ Need to check if sachin name was available in sheet 2
2. If the value is matching then it needs to check the adjacent cell value (column B) in both the sheets. ___ If Sachin name is available in Sheet 2 then it needs to compare the performance score with sheet 1
3. If the value in sheet 2 is greater than the value in sheet 1 then the entire row (from column B to column Z) needs to be copy paste to sheet 1. __ if Sachin performance is score is more in sheet 2 then the data in sheet 1 needs to be replaced by data in sheet 2 for sachin row.

 

[mumps]

if Sachin performance is score is more in sheet 2 then the data in sheet 1 needs to be replaced by data in sheet 2 for sachin row.

This part I understand. You want to replace the existing data in Sheet1. However, in order to do this, each row of data needs to have a unique identifier. The name in column A can be the unique identifier as long as there are no duplicate names. Can duplicate names exist in column A?

If the value in sheet 2 is greater than the value in sheet 1 then the entire row (from column B to column Z) needs to be copy paste to sheet 1.

This part is still not clear. Where in Sheet1 do you want to paste the data?
Also, it is hard to work with pictures. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your two sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Sunil113]

This part I understand. You want to replace the existing data in Sheet1. However, in order to do this, each row of data needs to have a unique identifier. The name in column A can be the unique identifier as long as there are no duplicate names. Can duplicate names exist in column A?

This part is still not clear. Where in Sheet1 do you want to paste the data?
Also, it is hard to work with pictures. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your two sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

Thank you for your time.
yes, each row has a unique Identifier from the attached mini sheet we can consider either name or ID.
No, we don't have a duplicate name in Column A.

Background Story:
Every individual/team has a different statement of work. By end of the day/month/year, they will update their work progress in their respective trackers.
Now, I have a macro to combine all of their tracker's data in a single sheet (Sheet 2) and then copy it to Main Tracker (Sheet 1).
As master tracker is used to check all the work it will be having more columns when compared to individual trackers.
In individual trackers employees might update the remarks/dates/any concerns.
When I run the macro again all the data from their tracker's will be copied to Sheet 2 and again the data will be copied to Master Tracker (Sheet 1).
By following the above process, I will be having Duplicated data in Sheet 1(Sachin with performance score 99 already in sheet 1 and again sachin with performance score 100 data copied from sheet 2) and if there is same data in sheet 2 after copying to sheet 1, I am using remove duplicates with col A & col B which is taking care of duplicates with same name & performance score in both the sheets.

So, I need a way where data in sheet 1 Column A it needs to check the data in sheet 2 column A, if it matches then it needs to check the right adjacent cell and if the value is more than the value in sheet 1 then that particular row needs to be copied to sheet 1 replacing the old data.
At last, the main tracker (Sheet 1) needs to show as Sachin with performance score 100 and his remarks.

Hope you are clear.

Below is the sample data.

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Employee ID	Employee	Performance Score	Work Due	Est Comp	Comp	Task	Campus	Building	Remarks	Priority	Budget Allocated	Budget Approved by	Budget Utilised	Bal	Head Count	Status
2	1	Dhoni	33	09/23/2023	09/23/2023		Developer		7				Gavaskar			11	Work Started
3	2	Ishant	33	09/23/2023	09/23/2023		Helper		9				Gavaskar			6	Work Started
4	3	Kholi	33	09/23/2023	09/23/2023		IT		6				Gavaskar			15	Work Started
5	4	Kumar	10	09/23/2023	09/23/2023		data Engineer		2				Gavaskar			28	Assignment of Work
6	5	Rahul	33	10/23/2022	10/23/2022		Security		5				Gavaskar			43	Work Started
7	6	Rohit	33	09/23/2023	09/23/2023		Helper		8				Gavaskar			25	Work Started
8	7	Sachin	99	09/25/2022	09/25/2022		Admin		4				Gavaskar			39	Task Completed with errors
9	8	Sai	67	09/23/2023	09/23/2023		Installation		3				Gavaskar			4	Phase -2 Completed
10	9	Sunil	33	09/23/2023	09/23/2023		Statician		1				Gavaskar			18	Work Started
Sheet1

Cell Formulas
Range	Formula
Q2:Q10	Q2	=VLOOKUP(C2,Sheet3!E$3:F$14,2,0)

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J
1	9	Sunil	33	09/23/2023	09/23/2023		Statician		1
2	4	Kumar	10	09/23/2023	09/23/2023		data Engineer		2
3	8	Sai	67	09/23/2023	09/23/2023		Installation		3
4	7	Sachin	100	09/25/2022	09/25/2022	09/23/2022	Admin		4	Task Completed - Assigned seats to all
5	5	Rahul	75	10/23/2022	10/23/2022		Security		5	Security patch updated with errors - Clearing errors
6	3	Kholi	33	09/23/2023	09/23/2023		IT		6
7	1	Dhoni	50	09/23/2023	09/23/2023		Developer		7	WIP
8	6	Rohit	33	09/23/2023	09/23/2023		Helper		8
9	2	Ishant	33	09/23/2023	09/23/2023		Helper		9
Sheet2

Book1.xlsx
	E	F
3	0	Need to Start
4	5	Head Count Required
5	10	Assignment of Work
6	33	Work Started
7	50	Phase -1 Completed
8	58	Phase -1 Testing Completed
9	67	Phase -2 Completed
10	70	Phase -2 Testing Completed
11	75	Phase -3 Completed
12	80	Phase -3 Testing Completed
13	99	Task Completed with errors
14	100	Completed
Sheet3

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Employee ID	Employee	Performance Score	Work Due	Est Comp	Comp	Task	Campus	Building	Remarks	Priority	Budget Allocated	Budget Approved by	Budget Utilised	Bal	Head Count	Status
2	1	Dhoni	33	09/23/2023	09/23/2023		Developer		7				Gavaskar			11	Work Started
3	2	Ishant	33	09/23/2023	09/23/2023		Helper		9				Gavaskar			6	Work Started
4	3	Kholi	33	09/23/2023	09/23/2023		IT		6				Gavaskar			15	Work Started
5	4	Kumar	10	09/23/2023	09/23/2023		data Engineer		2				Gavaskar			28	Assignment of Work
6	5	Rahul	33	10/23/2022	10/23/2022		Security		5				Gavaskar			43	Work Started
7	6	Rohit	33	09/23/2023	09/23/2023		Helper		8				Gavaskar			25	Work Started
8	7	Sachin	99	09/25/2022	09/25/2022		Admin		4				Gavaskar			39	Task Completed with errors
9	8	Sai	67	09/23/2023	09/23/2023		Installation		3				Gavaskar			4	Phase -2 Completed
10	9	Sunil	33	09/23/2023	09/23/2023		Statician		1				Gavaskar			18	Work Started
11	9	Sunil	33	09/23/2023	09/23/2023		Statician		1
12	4	Kumar	10	09/23/2023	09/23/2023		data Engineer		2
13	8	Sai	67	09/23/2023	09/23/2023		Installation		3
14	7	Sachin	100	09/25/2022	09/25/2022	09/23/2022	Admin		4	Task Completed - Assigned seats to all
15	5	Rahul	75	10/23/2022	10/23/2022		Security		5	Security patch updated with errors - Clearing errors
16	3	Kholi	33	09/23/2023	09/23/2023		IT		6
17	1	Dhoni	50	09/23/2023	09/23/2023		Developer		7	WIP
18	6	Rohit	33	09/23/2023	09/23/2023		Helper		8
19	2	Ishant	33	09/23/2023	09/23/2023		Helper		9
Sheet1

Cell Formulas
Range	Formula
Q2:Q10	Q2	=VLOOKUP(C2,Sheet3!E$3:F$14,2,0)

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Employee ID	Employee	Performance Score	Work Due	Est Comp	Comp	Task	Campus	Building	Remarks	Priority	Budget Allocated	Budget Approved by	Budget Utilised	Bal	Head Count	Status
2	1	Dhoni	50	09/23/2023	09/23/2023		Developer		7	WIP			Gavaskar			11	Phase -1 Completed
3	2	Ishant	33	09/23/2023	09/23/2023		Helper		9				Gavaskar			6	Work Started
4	3	Kholi	33	09/23/2023	09/23/2023		IT		6				Gavaskar			15	Work Started
5	4	Kumar	10	09/23/2023	09/23/2023		data Engineer		2				Gavaskar			28	Assignment of Work
6	5	Rahul	75	10/23/2022	10/23/2022		Security		5	Security patch updated with errors - Clearing errors			Gavaskar			43	Phase -3 Completed
7	6	Rohit	33	09/23/2023	09/23/2023		Helper		8				Gavaskar			25	Work Started
8	7	Sachin	100	09/25/2022	09/25/2022	09/23/2022	Admin		4	Task Completed - Assigned seats to all			Gavaskar			39	Completed
9	8	Sai	67	09/23/2023	09/23/2023		Installation		3				Gavaskar			4	Phase -2 Completed
10	9	Sunil	33	09/23/2023	09/23/2023		Statician		1				Gavaskar			18	Work Started
Sheet1

Cell Formulas
Range	Formula
Q2:Q10	Q2	=VLOOKUP(C2,Sheet3!E$3:F$14,2,0)

 

[mumps]

that particular row needs to be copied to sheet 1 replacing the old data.

You have more columns in Sheet1 than in Sheet2. If the row from Sheet2 replaces the row in Sheet1, you will lose some of the columns in Sheet1. Please clarify in detail.

 

[Sunil113]

You have more columns in Sheet1 than in Sheet2. If the row from Sheet2 replaces the row in Sheet1, you will lose some of the columns in Sheet1. Please clarify in detail.

Is it possible to copy the data in sheet 2 of certain range instead of entire row. (Copy Only Cells with data) to sheet 1

 

[mumps]

Try:

Sub CompareData()
    Application.ScreenUpdating = False
    Dim i As Long, srcWS As Worksheet, desWS As Worksheet, v1 As Variant, v2 As Variant, dic As Object
    Set desWS = Sheets("Sheet1")
    Set srcWS = Sheets("Sheet2")
    v1 = desWS.Range("A2", desWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 3).Value
    v2 = srcWS.Range("A1", srcWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 3).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v2) To UBound(v2)
        If Not dic.exists(v2(i, 2)) Then
            dic.Add v2(i, 2), v2(i, 3)
        End If
    Next i
    For i = LBound(v1) To UBound(v1)
        If dic.exists(v1(i, 2)) Then
            If dic(v1(i, 2)) > v1(i, 3) Then
                With srcWS
                    .Range("A" & i).Resize(, 9).Copy desWS.Range("A" & i + 1)
                    .Range("J" & i).Copy desWS.Range("Q" & i + 1)
                End With
            End If
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[Sunil113]

Thank you So Much
this really helps to solve the issue.
Will let you know if I face any issues.
if possible could you please explain the code in brief.

 

[mumps]

You are very welcome. I hope this helps:

Sub CompareData()
    Application.ScreenUpdating = False
    Dim i As Long, srcWS As Worksheet, desWS As Worksheet, v1 As Variant, v2 As Variant, dic As Object
    Set desWS = Sheets("Sheet1")
    Set srcWS = Sheets("Sheet2")
    v1 = desWS.Range("A2", desWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 3).Value 'assigns values in columns A, B and C of Sheet1 to an array
    v2 = srcWS.Range("A1", srcWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 3).Value 'assigns values in columns A, B and C of Sheet2 to an array
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v2) To UBound(v2) 'loops through names in Sheet2
        If Not dic.exists(v2(i, 2)) Then
            dic.Add v2(i, 2), v2(i, 3) 'adds names to the dictionary
        End If
    Next i
    For i = LBound(v1) To UBound(v1) 'loops through names in Sheet1
        If dic.exists(v1(i, 2)) Then 'checks if names exist in the dictionary
            If dic(v1(i, 2)) > v1(i, 3) Then 'compares values in column C of both sheets
                With srcWS 'if value in column C of Sheet2 is greater than value in column C of Sheet1---
                    .Range("A" & i).Resize(, 9).Copy desWS.Range("A" & i + 1) 'copies A:I from Sheet2 to Sheet1
                    .Range("J" & i).Copy desWS.Range("Q" & i + 1) 'copies column J from Sheet2 to column Q of Sheet1
                End With
            End If
        End If
    Next i
    Application.ScreenUpdating = True
End Sub