Split Excel spreadsheets into multiple spreadsheets

1

I am looking to take an Excel spreadssheet and split it into multiple Excel spreadsheets with their values pro-rated across the output spreadsheets.
Spreadsheet A(A) contains a list of information about employees. It tracks things like the hours they’ve worked this month, and the department they work in. In this case department is called ‘Business Unit’.
Look at Spreadsheet B(B).
MSA and TSSSA are kinds of Employment Equity in A.
I should split 2 sheets from A&B. MSA, TSSSA sheet!
Their columns are the same as A.
But Employment Equity must be changed to MSA or TSSSA.
And values of ‘hours’ column must be calculated.
‘hours’ column of A * MSA or TSSSA of B
for example, Mary’s hours of MSA is 200.3=6 and TSSSA is 200.7=14.
I am looking for someone can help me with this problem.Spreadsheet A.xlsx (12.3 KB) Spreadsheet B.xlsx (8.6 KB)

2

Hi @Newday and welcome to the Community!

You can use derived tables to split data from a spreadsheet into two datasets, and then export each derived table into an Excel spreadsheet. See this tutorial topics:

Derived tables
Filtering data
Calculations
Exporting data

It might be a good idea to go through at least the Basic tutorial before you start working with EasyMoprh. You can start here: https://easymorph.com/learn.html

3

Thank you for the good reply.
As you know, I am a beginner for EasyMorph.
I tried to solve the problem but It’s not so easy for me.
I think it’s no problem for you and I hope you sending answer of the problem.

Kind Regards.
Newday

4

How exactly did you try to solve this problem in EasyMorph and what didn’t work? Can you post your project?

5

Yes, I want you to refine this.
Thank you.work.morph (7.9 KB)

6

OK, I see that you’ve done almost everything. But the both tables are exported into the same file (output.xlsx). If you want to export into different spreadsheets you should specify different file names.

See below your project reworked:

  • Export done into two different spreadsheets

work.morph (8.0 KB)

7

Thank you.
I want to have two sheets, MSA&TSSSA in only one file.
How can I do this?

8

Use the Synchronize action to avoid simultaneous export into the same spreadsheet. This question was answered in this topic: Unable to update two worksheets in the same workbook from two easy morph table

See below your project reworked:

  • Added synchronization
  • Export into two sheets of the same spreadsheet

work.morph (8.0 KB)

As it wasn’t clear to me what the problem was - a few suggestions:

  • Search the forum before posting a question - it may have already been answered
  • If you’re getting an error - post a screenshot with this error, or at least include in your post the error message
9

Thank you.
I have some more questions.
I want to output file as A.
I send output file I want.
Please help me more.Book1.xlsx (10.3 KB)

10

I’m not sure I understand the question. Can you elaborate?

11

I want to make the output file like A.
I sent you output file I want.
I send again.
Book1.xlsx (11.9 KB)

12

EasyMorph doesn’t do Excel formatting. Although, it can replace sheets in an existing Excel report. Make a sheet that contains only data and another sheet that has a formatted table with the same data linked to the data sheet using Excel references. Use EasyMorph to replace the data sheet.

13

Thank you.
How can I replace the data sheet by using EasyMorph?
I hope you helping me about it.
Sorry.

14

The “Export into Excel spreadsheet” action has a mode for replacing sheets in existing files.

image

15

I try so but it doesn’t work well.
I send it.
I want you to refine it.
Thank you.

16

work.morph (8.0 KB)

17

I believe my instructions were clear. But you don’t follow them.

Can’t help you more, sorry.

18

Sorry. I sent old file. I will send it again.
I need more help from you.
Please help me.
I am really beginner for this tool.work.morph (8.0 KB)

19

Please help me.

20

Hi @Newday

My suggestion is as follows:

  1. Create the file called output.xlsx (with Excel)
  2. Create 2 tabs - one MSA and other TSSSA (With Excel)
  3. Then use the Replace sheet in existing file for both actions on export (Easymorph)

I find that it works well.

To get the formatting correct, follows Dmitry’s advise as provided above.

R