Repeating Rows

Izila

New Member
Joined
Aug 27, 2019
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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?
IDYearhouse numberChildren
(constant)
Adults
(constant)
Income
(Avg)
Distance to work (sum)milk price (Avg)Employees at Work (sum)
23620131034661211
236201310344489

edited row:
ID
Yearhouse numberChildren
(constant)
Adults
(constant)
Income
(Avg)
Distance to work (sum)milk price (Avg)Employees at Work (sum)
236201310345101020
 
Last edited:

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
like this?
with Power Query
IDYearhouse numberChildren (constant)Adults (constant)Income (Avg)Distance to work (sum)milk price (Avg)Employees at Work (sum)
23620131034661211
236201310344489
IDYearhouse numberChildren (constant)Adults (constant)Income (Avg)Distance to work (sum)milk price (Avg)Employees at Work (sum)
236201310345101020

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

New Member
Joined
Jun 22, 2020
Messages
14
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
Book1
ABCDEFGHI
4AvgSumAvgSum
5IDYearhouse numberChildrenAdultsIncomeDistance to work (sum)milk price (Avg)Employees at Work (sum)
623620131034661211
7236201310344489
823720141245891412
923720141245652014
10
11IDYearhouse numberChildrenAdultsIncomeDistance to work (sum)milk price (Avg)Employees at Work (sum)
12236201310345101020
13237201412457141726
Sheet1
Cell Formulas
RangeFormula
A12:A13A12=UNIQUE(A6:A9)
B12:F13,H12:H13B12=AVERAGEIF($A$6:$A$9,$A12,XLOOKUP(B$11,$A$5:$I$5,$A$6:$I$9))
G12:G13,I12:I13G12=SUMIF($A$6:$A$9,$A12,XLOOKUP(G$11,$A$5:$I$5,$A$6:$I$9))
Dynamic array formulas.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 18, 2019
Messages
884
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Some Changes
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
884
Office Version
  1. 365
Platform
  1. Windows
Book1
ABCDEFGHIJKLMNOPQRST
1IDYearhouse numberChildren (constant)Adults (constant)Income (Avg)Distance to work (sum)milk price (Avg)Employees at Work (sum)IDYearhouse numberChildren (constant)Adults (constant)Income (Avg)Distance to work (sum)milk price (Avg)Employees at Work (sum)
223620131034661211236201310345101020
323620131034448923720149431011010
423720149431011010238201494311252
5238201494311252
Sheet1
Cell Formulas
RangeFormula
M2:Q4,S2:S4M2=AVERAGE(IF($A$2:$A$5=$L2,B$2:B$5))
R2:R4,T2:T4R2=SUM(IF($A$2:$A$5=$L2,G$2:G$5))
 

Izila

New Member
Joined
Aug 27, 2019
Messages
14
Office Version
  1. 365
Platform
  1. Windows
like this?
with Power Query
IDYearhouse numberChildren (constant)Adults (constant)Income (Avg)Distance to work (sum)milk price (Avg)Employees at Work (sum)
23620131034661211
236201310344489
IDYearhouse numberChildren (constant)Adults (constant)Income (Avg)Distance to work (sum)milk price (Avg)Employees at Work (sum)
236201310345101020

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
Book1
ABCDEFGHI
4AvgSumAvgSum
5IDYearhouse numberChildrenAdultsIncomeDistance to work (sum)milk price (Avg)Employees at Work (sum)
623620131034661211
7236201310344489
823720141245891412
923720141245652014
10
11IDYearhouse numberChildrenAdultsIncomeDistance to work (sum)milk price (Avg)Employees at Work (sum)
12236201310345101020
13237201412457141726
Sheet1
Cell Formulas
RangeFormula
A12:A13A12=UNIQUE(A6:A9)
B12:F13,H12:H13B12=AVERAGEIF($A$6:$A$9,$A12,XLOOKUP(B$11,$A$5:$I$5,$A$6:$I$9))
G12:G13,I12:I13G12=SUMIF($A$6:$A$9,$A12,XLOOKUP(G$11,$A$5:$I$5,$A$6:$I$9))
Dynamic array formulas.
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

New Member
Joined
Aug 27, 2019
Messages
14
Office Version
  1. 365
Platform
  1. Windows
Book1
ABCDEFGHIJKLMNOPQRST
1IDYearhouse numberChildren (constant)Adults (constant)Income (Avg)Distance to work (sum)milk price (Avg)Employees at Work (sum)IDYearhouse numberChildren (constant)Adults (constant)Income (Avg)Distance to work (sum)milk price (Avg)Employees at Work (sum)
223620131034661211236201310345101020
323620131034448923720149431011010
423720149431011010238201494311252
5238201494311252
Sheet1
Cell Formulas
RangeFormula
M2:Q4,S2:S4M2=AVERAGE(IF($A$2:$A$5=$L2,B$2:B$5))
R2:R4,T2:T4R2=SUM(IF($A$2:$A$5=$L2,G$2:G$5))
thank you
 

Watch MrExcel Video

Forum statistics

Threads
1,123,322
Messages
5,600,951
Members
414,417
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top