1 excel sheet with many reports

Phill032

Board Regular
Joined
Nov 9, 2016
Messages
51
Hi, i have the below CSV file that when i download from our system has all reports on the one sheet. How can i load convert this to each report being on a different sheet/table in power query. A small snippet of the data below.
Note the headers of each report i have highlighted.
Department Profit DSA Report2023090880713368.csv
ABCDEFG
1Company_NameBranch_NameDepartment_NameOriginal_MTD_Budget_AmtMTD_Budget_AmtMTD_Actual_AmtMTD_Variance_Amt
2OSPK Pty LtdPARK KIANew Vehicles4219742197-25611.7267808.72
3OSPK Pty LtdPARK KIAUsed Vehicles-4064-4064-85187.1881123.18
4OSPK Pty LtdPARK KIAAftermarket296542965423519.46134.6
5SourceCompany_KeyCompany_NameBranch_KeyBranch_NameDepartment_OrderPAndL_View_Order
6Sales/COGS1OSPK Pty Ltd1PARK KIA61
7Sales/COGS1OSPK Pty Ltd1PARK KIA71
8SourceCompany_KeyCompany_NameBranch_KeyBranch_NameSection_IDAccount_Group_ID
9Expense/Income1OSPK Pty Ltd1PARK KIA6
10Expense/Income1OSPK Pty Ltd1PARK KIA6
11Expense/Income1OSPK Pty Ltd1PARK KIA661
12Expense/Income1OSPK Pty Ltd1PARK KIA661
13SourceCompany_KeyCompany_NameBranch_KeyBranch_NameDepartment_OrderDepartment_ID
14Statistic1OSPK Pty Ltd1PARK KIA66
15Statistic1OSPK Pty Ltd1PARK KIA77
16Statistic1OSPK Pty Ltd1PARK KIA1010
17Statistic1OSPK Pty Ltd1PARK KIA-1-1
18Statistic1OSPK Pty Ltd1PARK KIA66
19Statistic1OSPK Pty Ltd1OSBORNE PARK KIA77
Department Profit DSA Report202
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
I don't think that PQ will help you in this case, because you can only get one sheet result from PQ.

I will also watch this thread to hear from the PQ experts for ideas, but in the meantime, a simple VBA code might help you for this in case each header contains at least one same column name, like Company_Name as I can see in the sample data.

VBA Code:
Sub separateData()
Dim rng As Range
Dim fnd1 As Range
Dim fnd2 As Range
Dim block As Range
Dim sht As Worksheet
    
    Cells(1, 1).Activate
    Selection.CurrentRegion.Cells(Selection.CurrentRegion.Rows.Count + 1, 1) = "Company_Name"
    Set rng = Selection.CurrentRegion
    
    Set fnd1 = rng.Cells(1, 1)
    Set fnd2 = rng.Cells.Find("Company_Name")
    
    Do
        Set block = Range(fnd1.EntireRow.Cells(1, 1), fnd2.EntireRow.Offset(-1).Cells(1, 1)).Resize(, rng.Columns.Count)
        Set sht = ActiveWorkbook.Worksheets.Add(after:=ActiveSheet)
        block.Copy sht.Cells(1, 1)
        Set fnd1 = fnd2
        Set fnd2 = rng.FindNext(fnd2)
    Loop Until fnd2.Row <= fnd1.Row
    
    fnd1.EntireRow.Delete xlShiftUp
    
End Sub
 
Upvote 0
I don't think that PQ will help you in this case, because you can only get one sheet result from PQ.

I will also watch this thread to hear from the PQ experts for ideas, but in the meantime, a simple VBA code might help you for this in case each header contains at least one same column name, like Company_Name as I can see in the sample data.

VBA Code:
Sub separateData()
Dim rng As Range
Dim fnd1 As Range
Dim fnd2 As Range
Dim block As Range
Dim sht As Worksheet
   
    Cells(1, 1).Activate
    Selection.CurrentRegion.Cells(Selection.CurrentRegion.Rows.Count + 1, 1) = "Company_Name"
    Set rng = Selection.CurrentRegion
   
    Set fnd1 = rng.Cells(1, 1)
    Set fnd2 = rng.Cells.Find("Company_Name")
   
    Do
        Set block = Range(fnd1.EntireRow.Cells(1, 1), fnd2.EntireRow.Offset(-1).Cells(1, 1)).Resize(, rng.Columns.Count)
        Set sht = ActiveWorkbook.Worksheets.Add(after:=ActiveSheet)
        block.Copy sht.Cells(1, 1)
        Set fnd1 = fnd2
        Set fnd2 = rng.FindNext(fnd2)
    Loop Until fnd2.Row <= fnd1.Row
   
    fnd1.EntireRow.Delete xlShiftUp
   
End Sub

Hi, thanks for that. This VBA does work not want i was planning but i think i can make it work for me.

Cheers!
 
Upvote 0
The VBA works exactly as needed for the provided sample data. Takes each section and put that range into a separate worksheet.
However, as you also mentioned, it is just "a small snippet of the data" and the code surely needs to be adjusted for the real data you have.

Let us know if you have any difficulties adjusting it.
 
Upvote 0
The VBA works exactly as needed for the provided sample data. Takes each section and put that range into a separate worksheet.
However, as you also mentioned, it is just "a small snippet of the data" and the code surely needs to be adjusted for the real data you have.

Let us know if you have any difficulties adjusting it.
So all reports seperate out except 1, is there a chance that you could add like a second criteria. The report does not have the Company_Name but does have a Company_Key header name. it's honestly not much hassle to cut and paste it into a new page but if it is possible and not too much hassle??
 
Upvote 0
So, there is no same header repeating on each section header.

If "Company_" stil exists in each header (not in the data), then I think we can use the following modified code.

VBA Code:
Sub separateData()
Dim rng As Range
Dim fnd1 As Range
Dim fnd2 As Range
Dim block As Range
Dim sht As Worksheet
Dim strKey As String

    strKey = "Company_"
    
    Cells(1, 1).Activate
    Selection.CurrentRegion.Cells(Selection.CurrentRegion.Rows.Count + 1, 1) = strKey
    Set rng = Selection.CurrentRegion
    
    Set fnd1 = rng.Cells(1, 1)
    Set fnd2 = rng.Cells.Find(strKey)
    
    Do
        If fnd1.Row <> fnd2.Row Then
            Set block = Range(fnd1.EntireRow.Cells(1, 1), fnd2.EntireRow.Offset(-1).Cells(1, 1)).Resize(, rng.Columns.Count)
            Set sht = ActiveWorkbook.Worksheets.Add(after:=ActiveSheet)
            block.Copy sht.Cells(1, 1)
        End If
        Set fnd1 = fnd2
        Set fnd2 = rng.FindNext(fnd2)
    Loop Until fnd2.Row < fnd1.Row
    
    fnd1.EntireRow.Delete xlShiftUp
    
End Sub
 
Upvote 0
Solution
So, there is no same header repeating on each section header.

If "Company_" stil exists in each header (not in the data), then I think we can use the following modified code.

VBA Code:
Sub separateData()
Dim rng As Range
Dim fnd1 As Range
Dim fnd2 As Range
Dim block As Range
Dim sht As Worksheet
Dim strKey As String

    strKey = "Company_"
   
    Cells(1, 1).Activate
    Selection.CurrentRegion.Cells(Selection.CurrentRegion.Rows.Count + 1, 1) = strKey
    Set rng = Selection.CurrentRegion
   
    Set fnd1 = rng.Cells(1, 1)
    Set fnd2 = rng.Cells.Find(strKey)
   
    Do
        If fnd1.Row <> fnd2.Row Then
            Set block = Range(fnd1.EntireRow.Cells(1, 1), fnd2.EntireRow.Offset(-1).Cells(1, 1)).Resize(, rng.Columns.Count)
            Set sht = ActiveWorkbook.Worksheets.Add(after:=ActiveSheet)
            block.Copy sht.Cells(1, 1)
        End If
        Set fnd1 = fnd2
        Set fnd2 = rng.FindNext(fnd2)
    Loop Until fnd2.Row < fnd1.Row
   
    fnd1.EntireRow.Delete xlShiftUp
   
End Sub
Yes, that did the trick.. perfect. thanks so much for your help!!
 
Upvote 0
Yes, that did the trick.. perfect. thanks so much for your help!!
You're welcome. Glad to hear it helps.

That would be great if you could mark the post as the solution that answered the question in order to help future readers.
 
Upvote 0

Forum statistics

Threads
1,223,317
Messages
6,171,419
Members
452,402
Latest member
siduslevis

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
Back
Top