Excel Online and Reports from Multiple Tabs

[FatalLordes]

Morning all

So I'm using Excel Online within OneDrive which has a workbook with multiple sheets. Sheet 1 has each row with a load of data relating to the unique entry in cell A. Sheet 2 has rows of data where multiple rows (not necessarily in order) may have info relating to that unique entry on Sheet 1. What I would like to do is create a report that would be listing all the data for each unique entry pulling from both Sheet 1 and Sheet 2. Something like:

Unique One, blah, blah blah, blah
Row One of data in Sheet 2 for Unique One
Row Two of data in Sheet 2 for Unique One
Row Three of data in Sheet 2 for Unique One
Unique Two, blah, blah blah, blah
Row One of data in Sheet 2 for Unique Two
Row Two of data in Sheet 2 for Unique Two
and so on

I know I could figure out how to do it in desktop Excel using VBA but, of course, VBA is not possible in Excel Online. So my question is, can Excel Online produce something like this or am I dreaming? And if it can, how would I look at going about it. I'm no expert but I have worked out to so some fancy reporting before by googling VBA and trial and error, but I have no idea how to approach this for Excel Online or if it is even possible. So I thought I would ask before I waste any time like if it just isn't possible.

Thanks in advance.

 

[plettieri]

Hello FatalLordes


You might try this

Creating a report in Excel Online that consolidates data from multiple sheets based on unique entries can be challenging without VBA. However, you can achieve this using a combination of formulas and possibly Power Query (if available in your version of Excel Online). Here's a step-by-step guide on how you can approach this:

Step 1: Prepare Your Data​

Ensure that your data in Sheet 1 and Sheet 2 is well-organized. For this example, let's assume:

• Sheet 1 has columns: Unique ID, Data1, Data2, Data3, etc.
• Sheet 2 has columns: Unique ID, RelatedData1, RelatedData2, etc.

Step 2: Create a Consolidated Report Using Formulas​

1. Create a New Sheet:
   • Add a new sheet to your workbook, let's call it Report.
2. List Unique Entries:
   • In the Report sheet, list all unique entries from Sheet 1. You can use the UNIQUE function to do this.
     excel
     
     =UNIQUE(Sheet1!A2:A100)
   • This will give you a list of all unique IDs from Sheet 1.
3. Pull Data from Sheet 1:
   • Next to each unique ID, use the VLOOKUP function to pull data from Sheet 1.
     excel
     
     =VLOOKUP(A2, Sheet1!$A$2:$D$100, 2, FALSE) ' For Data1
     =VLOOKUP(A2, Sheet1!$A$2:$D$100, 3, FALSE) ' For Data2
     =VLOOKUP(A2, Sheet1!$A$2:$D$100, 4, FALSE) ' For Data3
   • Adjust the column index numbers and ranges as needed.
4. Pull Data from Sheet 2:
   • Use the FILTER function to pull all related rows from Sheet 2.
     excel
     
     =FILTER(Sheet2!A2:C100, Sheet2!A2:A100 = A2)
   • This will pull all rows from Sheet 2 where the Unique ID matches the one in the Report sheet.

Example Layout​

Your Report sheet might look something like this:

Unique ID	Data1	Data2	Data3	RelatedData1	RelatedData2
Unique1	blah	blah	blah	Row1Data	Row1Data
				Row2Data	Row2Data
				Row3Data	Row3Data
Unique2	blah	blah	blah	Row1Data	Row1Data
				Row2Data	Row2Data
...	...	...	...	...	...

Step 3: Formatting and Adjustments​

• Merge Cells: You might need to merge cells in the Unique ID column to make the report more readable.
• Conditional Formatting: Use conditional formatting to highlight different sections or unique IDs.

Using Power Query (If Available)​

If your version of Excel Online supports Power Query, you can use it to combine data from multiple sheets more efficiently.

1. Load Data into Power Query:
   • Load both Sheet 1 and Sheet 2 into Power Query.
2. Merge Queries:
   • Use the Merge Queries feature to combine data from both sheets based on the Unique ID.
3. Transform Data:
   • Transform and shape the data as needed to create the desired report format.
4. Load Data Back to Excel:
   • Load the transformed data back to a new sheet in Excel.

Conclusion​

While Excel Online has some limitations compared to the desktop version, you can still achieve a consolidated report using a combination of functions like UNIQUE, VLOOKUP, and FILTER. If Power Query is available, it can significantly simplify the process. This approach should help you create the report you need without relying on VBA.


hope this helps
plettieri

 

[FatalLordes]

Hello FatalLordes


You might try this

Creating a report in Excel Online that consolidates data from multiple sheets based on unique entries can be challenging without VBA. However, you can achieve this using a combination of formulas and possibly Power Query (if available in your version of Excel Online). Here's a step-by-step guide on how you can approach this:

Step 1: Prepare Your Data​

Ensure that your data in Sheet 1 and Sheet 2 is well-organized. For this example, let's assume:

• Sheet 1 has columns: Unique ID, Data1, Data2, Data3, etc.
• Sheet 2 has columns: Unique ID, RelatedData1, RelatedData2, etc.

Step 2: Create a Consolidated Report Using Formulas​

1. Create a New Sheet:
   • Add a new sheet to your workbook, let's call it Report.
2. List Unique Entries:
   • In the Report sheet, list all unique entries from Sheet 1. You can use the UNIQUE function to do this.
     excel
     
     =UNIQUE(Sheet1!A2:A100)
   • This will give you a list of all unique IDs from Sheet 1.
3. Pull Data from Sheet 1:
   • Next to each unique ID, use the VLOOKUP function to pull data from Sheet 1.
     excel
     
     =VLOOKUP(A2, Sheet1!$A$2:$D$100, 2, FALSE) ' For Data1
     =VLOOKUP(A2, Sheet1!$A$2:$D$100, 3, FALSE) ' For Data2
     =VLOOKUP(A2, Sheet1!$A$2:$D$100, 4, FALSE) ' For Data3
   • Adjust the column index numbers and ranges as needed.
4. Pull Data from Sheet 2:
   • Use the FILTER function to pull all related rows from Sheet 2.
     excel
     
     =FILTER(Sheet2!A2:C100, Sheet2!A2:A100 = A2)
   • This will pull all rows from Sheet 2 where the Unique ID matches the one in the Report sheet.

Example Layout​

Your Report sheet might look something like this:

Unique ID	Data1	Data2	Data3	RelatedData1	RelatedData2
Unique1	blah	blah	blah	Row1Data	Row1Data
				Row2Data	Row2Data
				Row3Data	Row3Data
Unique2	blah	blah	blah	Row1Data	Row1Data
				Row2Data	Row2Data
...	...	...	...	...	...

Step 3: Formatting and Adjustments​

• Merge Cells: You might need to merge cells in the Unique ID column to make the report more readable.
• Conditional Formatting: Use conditional formatting to highlight different sections or unique IDs.

Using Power Query (If Available)​

If your version of Excel Online supports Power Query, you can use it to combine data from multiple sheets more efficiently.

1. Load Data into Power Query:
   • Load both Sheet 1 and Sheet 2 into Power Query.
2. Merge Queries:
   • Use the Merge Queries feature to combine data from both sheets based on the Unique ID.
3. Transform Data:
   • Transform and shape the data as needed to create the desired report format.
4. Load Data Back to Excel:
   • Load the transformed data back to a new sheet in Excel.

Conclusion​

While Excel Online has some limitations compared to the desktop version, you can still achieve a consolidated report using a combination of functions like UNIQUE, VLOOKUP, and FILTER. If Power Query is available, it can significantly simplify the process. This approach should help you create the report you need without relying on VBA.


hope this helps
plettieri

Thank you. I will try this. I'm pretty sure there is no power query in the free version but I'll certainly give it a whirl and let you know how I go.