Excel Formula to add lines dynamically based on the source data

[Balajibenz]

Hi Team,

I have data in sheet1 as below.

KW2_Gagu Personal & Polen.xlsx
	A	B	C	D	E	F	G	H	I	J
2	Firm	Number	Name	1/11/2021					Zone	Total
3	Gagu Personal	3439	Adamcova, Milena	14:18	22:30	8.2	0.83	7.37	OST	1077.0
4	Gagu Personal	3441	Balaz, Gabriel	14:19	22:18	7.98	0.83	7.15	TRK	571.0
5	Gagu Personal	3477	Basno, Dorina	14:05	22:29	8.4	0.5	7.9	TRK	341.0
6	Gagu Personal	1938	Belkania, Fridon	14:20	22:13	7.89	0.5	7.39	#N/A	#N/A
7	Gagu Personal	2435	Berzan, Cristin	3:45	13:43	9.97	0.75	9.22	TRK	830.0
8	Gagu Personal	2482	Bilici, Igor	0:00	0:00	0.0	0.0	0.0	MOP	1453.0
9	Gagu Personal	1688	Boji, Gheorghe	3:51	13:59	10.13	0.83	9.3	MOP	1619.0
10	Gagu Personal	1681	Boji, Romina	3:49	14:23	10.57	0.75	9.82	MOP	2300.0
11	Gagu Personal	2436	Boji, Ruslan	3:52	14:30	10.63	0.75	9.88	#N/A	#N/A
12	Gagu Personal	3442	Bozsuc, Vasile	12:47	21:59	9.2	0.83	8.37	MOP	1241.0
13	Gagu Personal	1849	Brezovy, Adam	5:16	14:38	9.37	0.83	8.54	TKK	931.0
14	Gagu Personal	3475	Burlacu, Andrian	14:17	22:17	8.0	0.5	7.5	TRK	410.0
Zeiterfassung

and data in sheet2 as below.

KW2_Gagu Personal & Polen.xlsx
	A	B	C	D	E
1	Name	Date	Firm	Zone	Total
2	Adamcova, Milena	1/11/2021	Gagu Personal	OST	1077
3	Adamcova, Milena	1/11/2021	Gagu Personal	TRK	100
4	Balaz, Gabriel	1/11/2021	Gagu Personal	TRK	571
5	Basno, Dorina	1/11/2021	Gagu Personal	TRK	341
6	Berzan, Cristin	1/11/2021	Gagu Personal	TRK	830
7	Bilici, Igor	1/11/2021	Gagu Personal	MOP	1453
8	Bilici, Igor	1/11/2021	Gagu Personal	TRK	133
9	Boji, Gheorghe	1/11/2021	Gagu Personal	MOP	1619
10	Boji, Romina	1/11/2021	Gagu Personal	MOP	2300
11	Bozsuc, Vasile	1/11/2021	Gagu Personal	MOP	1241
12	Brezovy, Adam	1/11/2021	Gagu Personal	TKK	931
all picks

what i am looking for is to compare between two sheets and if there are mutliple lines for a person sheet2 then that line needs to added to sheet1.

for example take the first name from the list - Adamcova, Milena. we have one line for in sheet1 for the zone "OST" but in sheet2 we can two lines one for OST and other for "TRK". what I am looking for is to add this additional line(along with zone and total) from sheet2 to sheet1 right under the existing line (its not mandatory, we can add it in the last too so that i can sort it later).

there might more than 2 lines for person in few scenarios. thank you in advance.

 

[maabadi]

Try this:

Sub ADDValuesAndRows()
Dim i As Long, j As Long, Lr1 As Long, Lr2 As Long, Sh1 As Worksheet, Sh2 As Worksheet
Dim K As Long, L1 As Long, L2 As Long
Set Sh1 = Sheets("Sheet1")
Set Sh2 = Sheets("Sheet2")
Lr1 = Sh1.Range("A" & Rows.Count).End(xlUp).Row
Lr2 = Sh2.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To Lr1 + Lr2
On Error Resume Next
K = Application.WorksheetFunction.Match(Sh1.Range("C" & i), Sh2.Range("A1:A" & Lr2), 0)
If K > 0 Then
Debug.Print K
L1 = Application.WorksheetFunction.CountIf(Sh2.Range("A2:A" & Lr1), Sh1.Range("C" & i).Value)
L2 = Application.WorksheetFunction.CountIf(Sh1.Range("C2:C" & Lr2), Sh1.Range("C" & i).Value)
Debug.Print L1
Debug.Print L2
End If
If L1 > L2 And L2 > 0 Then
Sh1.Rows(i + 1).Resize(L1 - L2).Insert
Sh1.Range("A" & i + 1 & ":A" & i + L1 - L2).Value = Sh2.Range("C" & K + 1 & ":C" & K + L1 - L2).Value
Sh1.Range("C" & i + 1 & ":C" & i + L1 - L2).Value = Sh2.Range("A" & K + 1 & ":A" & K + L1 - L2).Value
Sh1.Range("I" & i + 1 & ":I" & i + L1 - L2).Value = Sh2.Range("D" & K + 1 & ":D" & K + L1 - L2).Value
Sh1.Range("J" & i + 1 & ":J" & i + L1 - L2).Value = Sh2.Range("E" & K + 1 & ":E" & K + L1 - L2).Value
i = i + L1 - L2
End If
Next i
End Sub

 

[Balajibenz]

Try this:

Sub ADDValuesAndRows()
Dim i As Long, j As Long, Lr1 As Long, Lr2 As Long, Sh1 As Worksheet, Sh2 As Worksheet
Dim K As Long, L1 As Long, L2 As Long
Set Sh1 = Sheets("Sheet1")
Set Sh2 = Sheets("Sheet2")
Lr1 = Sh1.Range("A" & Rows.Count).End(xlUp).Row
Lr2 = Sh2.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To Lr1 + Lr2
On Error Resume Next
K = Application.WorksheetFunction.Match(Sh1.Range("C" & i), Sh2.Range("A1:A" & Lr2), 0)
If K > 0 Then
Debug.Print K
L1 = Application.WorksheetFunction.CountIf(Sh2.Range("A2:A" & Lr1), Sh1.Range("C" & i).Value)
L2 = Application.WorksheetFunction.CountIf(Sh1.Range("C2:C" & Lr2), Sh1.Range("C" & i).Value)
Debug.Print L1
Debug.Print L2
End If
If L1 > L2 And L2 > 0 Then
Sh1.Rows(i + 1).Resize(L1 - L2).Insert
Sh1.Range("A" & i + 1 & ":A" & i + L1 - L2).Value = Sh2.Range("C" & K + 1 & ":C" & K + L1 - L2).Value
Sh1.Range("C" & i + 1 & ":C" & i + L1 - L2).Value = Sh2.Range("A" & K + 1 & ":A" & K + L1 - L2).Value
Sh1.Range("I" & i + 1 & ":I" & i + L1 - L2).Value = Sh2.Range("D" & K + 1 & ":D" & K + L1 - L2).Value
Sh1.Range("J" & i + 1 & ":J" & i + L1 - L2).Value = Sh2.Range("E" & K + 1 & ":E" & K + L1 - L2).Value
i = i + L1 - L2
End If
Next i
End Sub

thanks mate, that helped

 

[maabadi]

You're Welcome & Thanks for Feedback.