Consolidate sheets horizontally i.e. side by side

Ramneek

New Member
Joined
Mar 19, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
Dear Sir,

I download a report in excel from a software. The report contains different tabs each containing details on quarterly basis. I want to consolidate all the tabs in one sheet to create a yearly summary but i want to append them side by side and not on the top of each other. The report looks like this
37AACCB6390F1ZN_GSTR3BR1_RECONCILED_Summary(2020-2021).xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
4SectionParticulars April MayJuneQuarter - 1
5TaxableIGSTCGSTSGSTCessTotalTaxableIGSTCGSTSGSTCessTotalTaxableIGSTCGSTSGSTCessTotalTaxableIGSTCGSTSGSTCessTotal
6GSTR-3B Supply Details 3.1(a) - Outward taxable supplies (Other than zero rated, nil rated and exempted)₹8,49,125.00₹0.00₹76,422.00₹76,422.00₹0.00₹10,01,969.00₹8,75,838.00₹0.00₹78,826.00₹78,826.00₹0.00₹10,33,490.00₹8,75,838.00₹0.00₹78,826.00₹78,826.00₹0.00₹10,33,490.00₹26,00,801.00₹0.00₹2,34,074.00₹2,34,074.00₹0.00₹30,68,949.00
73.1(b) - Outward taxable supplies (zero rated)₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00
83.1(c) - Other outward supplies (nil rated, exempted)₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00
93.1(e) - Non GST outward supplies₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00
103.2 - Inter-state supplies₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00
11Total from GSTR-3B (A)₹8,49,125.00₹0.00₹76,422.00₹76,422.00₹0.00₹10,01,969.00₹8,75,838.00₹0.00₹78,826.00₹78,826.00₹0.00₹10,33,490.00₹8,75,838.00₹0.00₹78,826.00₹78,826.00₹0.00₹10,33,490.00₹26,00,801.00₹0.00₹2,34,074.00₹2,34,074.00₹0.00₹30,68,949.00
12GSTR-1 Supply DetailsB2B Supplies₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00
13B2C Small Supplies₹8,49,125.00₹0.00₹76,421.25₹76,421.25₹0.00₹10,01,967.50₹8,75,838.00₹0.00₹78,825.42₹78,825.42₹0.00₹10,33,488.84₹8,75,838.00₹0.00₹78,825.42₹78,825.42₹0.00₹10,33,488.84₹26,00,801.00₹0.00₹2,34,072.09₹2,34,072.09₹0.00₹30,68,945.18
14B2C Large Supplies₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00
15Exports₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00
16Credit/Debit Notes - Registered₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00
17Credit/Debit Notes - Unregistered₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00
18Advance Receipts₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00
19Tax Adjustments₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00
20Nil Supplies₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00
21Total from GSTR-1 (B)₹8,49,125.00₹0.00₹76,421.25₹76,421.25₹0.00₹10,01,967.50₹8,75,838.00₹0.00₹78,825.42₹78,825.42₹0.00₹10,33,488.84₹8,75,838.00₹0.00₹78,825.42₹78,825.42₹0.00₹10,33,488.84₹26,00,801.00₹0.00₹2,34,072.09₹2,34,072.09₹0.00₹30,68,945.18
22Difference (A - B)₹0.00₹0.00₹1.00₹1.00₹0.00₹2.00₹0.00₹0.00₹1.00₹1.00₹0.00₹1.00₹0.00₹0.00₹1.00₹1.00₹0.00₹1.00₹0.00₹0.00₹2.00₹2.00₹0.00₹4.00
23
Q1 - APR-JUN
Cell Formulas
RangeFormula
O12:O20,O6:O10,I12:I20,I6:I10,AA12:AA20,U12:U20,U6:U10,AA6:AA10U6=SUM(P6:T6)
V12:Z20,V6:Z10V6=SUM(D6,J6,P6)
D11:AA11U11=SUM(U6:U9)
D21:AA21D21=SUM(D12:D20)
D22:AA22D22=ROUND(SUM(D11-D21), 0)
Named Ranges
NameRefers ToCells
rone_04='Q1 - APR-JUN'!$D$12:$H$20D21, V12, I12
rone_05='Q1 - APR-JUN'!$J$12:$N$20J21, V12, O12
rone_06='Q1 - APR-JUN'!$P$12:$T$20P21, U12:V12
rthrb_04='Q1 - APR-JUN'!$D$6:$H$10D11, V6, I6
rthrb_05='Q1 - APR-JUN'!$J$6:$N$10J11, V6, O6
rthrb_06='Q1 - APR-JUN'!$P$6:$T$10P11, U6:V6
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D22:AA23Cell Value=0textNO
D22:AA23Cell Value<>0textNO
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,418
Office Version
  1. 365
Platform
  1. Windows
  1. When the report comes from the other software into Excel, is there anything in rows 1:3 (you haven't shown those rows) and, if so, should that come from each sheet to the consolidated sheet too?

  2. Do you want a new worksheet with all data transferred to it or would it suffice to use the existing left hand sheet as a starting point and just append the data from the other sheets at the right of its data?
 

Ramneek

New Member
Joined
Mar 19, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
Sir,

1. There is no data in rows 1:3

2. Appending the data from the other sheets at the right of its data would suffice.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,418
Office Version
  1. 365
Platform
  1. Windows
Give this a try with a copy of your workbook.

VBA Code:
Sub Copy_Sheets()
  Dim i As Long
  
  For i = 2 To Sheets.Count
    Sheets(i).UsedRange.Copy Destination:=Sheets(1).Cells(5, Columns.Count).End(xlToLeft).Offset(-1, 1)
  Next i
End Sub
 

Ramneek

New Member
Joined
Mar 19, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
Dear Sir,

Worked perfectly. Thanks a lot. You have saved me hell lot of time.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,418
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the follow-up. :)
 

Forum statistics

Threads
1,136,321
Messages
5,675,064
Members
419,548
Latest member
wfarzand

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