Repeating Rows

Izila · Aug 14, 2020

Hi,

I have a large excel spreadsheet that repeats every two rows by id and year. however, some variables are constant( no command needed), some need to be averaged and added. I need to merge the two rows repeating, there are over 1000 rows repeating in the dataset. I have illustrated with an example below. how do I merge the two rows together and paste them rows into a new spreadsheet for each ID?

ID	Year	house number	Children (constant)	Adults (constant)	Income (Avg)	Distance to work (sum)	milk price (Avg)	Employees at Work (sum)
236	2013	10	3	4	6	6	12	11
236	2013	10	3	4	4	4	8	9

edited row:

ID	Year	house number	Children (constant)	Adults (constant)	Income (Avg)	Distance to work (sum)	milk price (Avg)	Employees at Work (sum)
236	2013	10	3	4	5	10	10	20

sandy666 · Aug 14, 2020

like this?
with Power Query



ID	Year	house number	Children (constant)	Adults (constant)	Income (Avg)	Distance to work (sum)	milk price (Avg)	Employees at Work (sum)
236	2013	10	3	4	6	6	12	11
236	2013	10	3	4	4	4	8	9


ID	Year	house number	Children (constant)	Adults (constant)	Income (Avg)	Distance to work (sum)	milk price (Avg)	Employees at Work (sum)
236	2013	10	3	4	5	10	10	20

Rich (BB code):

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Group = Table.Group(Source, {"ID", "Year", "house number", "Children (constant)", "Adults (constant)"}, {{"Income (Avg)", each List.Average([#"Income (Avg)"]), type number}, {"Distance to work (sum)", each List.Sum([#"Distance to work (sum)"]), type number}, {"milk price (Avg)", each List.Average([#"milk price (Avg)"]), type number}, {"Employees at Work (sum)", each List.Sum([#"Employees at Work (sum)"]), type number}})
in
    Group

Izila · Aug 14, 2020

thank you sandy666

CA Vijay Gupta · Aug 14, 2020

Book1

A

B

C

D

E

F

G

H

I

4

Avg

Sum

Avg

Sum

5

ID

Year

house number

Children

Adults

Income

Distance to work (sum)

milk price (Avg)

Employees at Work (sum)

6

236

2013

10

3

4

6

12

11

7

236

2013

10

3

4

8

9

8

237

2014

12

4

5

8

9

14

12

9

237

2014

12

4

5

6

5

20

14

10

11

ID

Year

house number

Children

Adults

Income

Distance to work (sum)

milk price (Avg)

Employees at Work (sum)

12

13

237

Sheet1

Cell Formulas
Range	Formula
A12:A13	A12	=UNIQUE(A6:A9)
B12:F13,H12:H13	B12	=AVERAGEIF($A$6:$A$9,$A12,XLOOKUP(B$11,$A$5:$I$5,$A$6:$I$9))
G12:G13,I12:I13	G12	=SUMIF($A$6:$A$9,$A12,XLOOKUP(G$11,$A$5:$I$5,$A$6:$I$9))
Dynamic array formulas.

sandy666 · Aug 14, 2020

Izila said:
thank you sandy666

You are welcome

Peter_SSs · Aug 14, 2020

Izila said:
repeats every two rows by id and year.

If that is strict and starts on row 2 of the active sheet with headers in row 1, you could also try this macro (with a copy of your workbook)

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

VBA Code:

Sub CombineTwoRows()
  Dim a As Variant
  Dim i As Long, j As Long, k As Long
  
  a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 9).Value
  k = 1
  For i = 2 To UBound(a) Step 2
    k = k + 1
    For j = 1 To 9
      a(k, j) = (a(i, j) + a(i + 1, j)) / 2
    Next j
    a(k, 7) = a(k, 7) * 2
    a(k, 9) = a(k, 9) * 2
  Next i
  With Sheets.Add(After:=ActiveSheet)
    .Range("A1").Resize(k, 9).Value = a
  End With
End Sub

CA_Punit · Aug 14, 2020

Some Changes

CA_Punit · Aug 14, 2020

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

1

ID

Year

house number

Children (constant)

Adults (constant)

Income (Avg)

Distance to work (sum)

milk price (Avg)

Employees at Work (sum)

ID

Year

house number

Children (constant)

Adults (constant)

Income (Avg)

Distance to work (sum)

milk price (Avg)

Employees at Work (sum)

2

236

2013

10

3

4

6

12

11

236

3

236

2013

10

3

4

8

9

237

4

237

2014

9

4

3

10

1

10

238

5

238

2014

9

4

3

11

2

5

2

Sheet1

Cell Formulas
Range	Formula
M2:Q4,S2:S4	M2	=AVERAGE(IF($A$2:$A$5=$L2,B$2:B$5))
R2:R4,T2:T4	R2	=SUM(IF($A$2:$A$5=$L2,G$2:G$5))

Izila · Aug 14, 2020

sandy666 said:
like this?
with Power Query

ID Year house number Children (constant) Adults (constant) Income (Avg) Distance to work (sum) milk price (Avg) Employees at Work (sum)
236 2013 10 3 4 6 6 12 11
236 2013 10 3 4 4 4 8 9
ID Year house number Children (constant) Adults (constant) Income (Avg) Distance to work (sum) milk price (Avg) Employees at Work (sum)
236 2013 10 3 4 5 10 10 20

Rich (BB code):

let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], Group = Table.Group(Source, {"ID", "Year", "house number", "Children (constant)", "Adults (constant)"}, {{"Income (Avg)", each List.Average([#"Income (Avg)"]), type number}, {"Distance to work (sum)", each List.Sum([#"Distance to work (sum)"]), type number}, {"milk price (Avg)", each List.Average([#"milk price (Avg)"]), type number}, {"Employees at Work (sum)", each List.Sum([#"Employees at Work (sum)"]), type number}}) in Group

CA Vijay Gupta said:
Book1
A B C D E F G H I
4 Avg Sum Avg Sum
5 ID Year house number Children Adults Income Distance to work (sum) milk price (Avg) Employees at Work (sum)
6 236 2013 10 3 4 6 6 12 11
7 236 2013 10 3 4 4 4 8 9
8 237 2014 12 4 5 8 9 14 12
9 237 2014 12 4 5 6 5 20 14
10
11 ID Year house number Children Adults Income Distance to work (sum) milk price (Avg) Employees at Work (sum)
12 236 2013 10 3 4 5 10 10 20
13 237 2014 12 4 5 7 14 17 26
Sheet1
Cell Formulas
Range Formula
A12:A13 A12 =UNIQUE(A6:A9)
B12:F13,H12:H13 B12 =AVERAGEIF($A$6:$A$9,$A12,XLOOKUP(B$11,$A$5:$I$5,$A$6:$I$9))
G12:G13,I12:I13 G12 =SUMIF($A$6:$A$9,$A12,XLOOKUP(G$11,$A$5:$I$5,$A$6:$I$9))
Dynamic array formulas.

Peter_SSs said:
If that is strict and starts on row 2 of the active sheet with headers in row 1, you could also try this macro (with a copy of your workbook)

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

VBA Code:

Sub CombineTwoRows() Dim a As Variant Dim i As Long, j As Long, k As Long a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 9).Value k = 1 For i = 2 To UBound(a) Step 2 k = k + 1 For j = 1 To 9 a(k, j) = (a(i, j) + a(i + 1, j)) / 2 Next j a(k, 7) = a(k, 7) * 2 a(k, 9) = a(k, 9) * 2 Next i With Sheets.Add(After:=ActiveSheet) .Range("A1").Resize(k, 9).Value = a End With End Sub

thank you

Izila · Aug 14, 2020

CA_Punit said:
Book1
A B C D E F G H I J K L M N O P Q R S T
1 ID Year house number Children (constant) Adults (constant) Income (Avg) Distance to work (sum) milk price (Avg) Employees at Work (sum) ID Year house number Children (constant) Adults (constant) Income (Avg) Distance to work (sum) milk price (Avg) Employees at Work (sum)
2 236 2013 10 3 4 6 6 12 11 236 2013 10 3 4 5 10 10 20
3 236 2013 10 3 4 4 4 8 9 237 2014 9 4 3 10 1 10 10
4 237 2014 9 4 3 10 1 10 10 238 2014 9 4 3 11 2 5 2
5 238 2014 9 4 3 11 2 5 2
Sheet1
Cell Formulas
Range Formula
M2:Q4,S2:S4 M2 =AVERAGE(IF($A$2:$A$5=$L2,B$2:B$5))
R2:R4,T2:T4 R2 =SUM(IF($A$2:$A$5=$L2,G$2:G$5))

thank you

Repeating Rows

Izila

New Member

Excel Facts

sandy666

Banned - Rules violations

Izila

New Member

CA Vijay Gupta

New Member

sandy666

Banned - Rules violations

Peter_SSs

MrExcel MVP, Moderator

CA_Punit

Well-known Member

CA_Punit

Well-known Member

Izila

New Member

Izila

New Member

Similar threads

Forum statistics

Share this page

Repeating Rows

New Member

Excel Facts

Banned - Rules violations

New Member

New Member

Banned - Rules violations

MrExcel MVP, Moderator

Well-known Member

Well-known Member

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock