Hi,
How do you join 5 sheets from 1 excel file? I thought I could do a formula to pull the sheets out and then join them?
I have 5 sheets Trans, Ref note, Trans date, account, sub
thanks
Hi @Hi2023
First you need to input the sheet names in the dynamic tool.
Then you need to used Dynamic input tool to connect all 5 sheets.
Many thanks
Shanker V
Step 1: Use the input tool
Select the Highlighted Option
It will read all the sheet names
Step 2:
Use the Dynamic input tool
@Hi2023
Check and let me know if it worked for your file.
Explaining a use case below for you.
Step 1: Input .xlsx file
Step 2: Input tool
Step 3:
Dynamic input tool
One thing you need to do prior importing the data's from all 5 sheets are , the Schema should be same.
i.e, the order of the columns in all 5 sheets should be same.
Or the data's will be merged but it will read in different column names matching to the first input sheet.
after the dynamic input tool, how do I retrieve and join only the 2 sheets I need and after the join I need to filter for dups within the data?
Sure, its possible.
Few minutes, will share the possible solution with use case.
I am breaking your question to 2 answer easily.
1. How to input only 2 sheets out of 5 sheets.
The best the way to do if filter the sheet names which you need to join.
Screenshot attached for your reference below.
Output:
Only 2 sheets datas will be inputted.
To answer your second question to remove the duplicates if any.
Use the Unique tool to remove the duplicates, post joining 2 sheets.
Hope this resolves your issue.
I assume its not working because those 2 sheets have diff columns? how can I fix this?
It is only pulling one sheet out of the filter.
thanks!
Can you share the excel sheet and which 2 sheets you are trying to input.
Will find a workaround solution.
It wont let me add a file for some reason
the 2 sheets I need within 1 excel file
First, is the 'trans' sheet which has 9 columns:
Trans, recvd, checkoutdate, insur, pt, address, city, state, loc#
Second, the other sheet I need from this is the 'acc' sheet which has 6 columns:
Trans(same as 'trans' sheet first column), alter#, accdrop, PCI#, update, date
I need to join these2 sheets together
Hi, so I would do this after the dynamic tool to pull the sheets out?
even with the columns being diff?
I guess I am not understanding how to join 3 sheets (now I need 3 sheets together) from one report?
I can pull the sheets out but then how do I get it to then merge the columns with a unique identifier as the trans #?
@Hi2023 Attached is how you will join multiple sheets into one excel.
To add another sheet to the workflow you can copy and paste the other inputs and paste it into the white space (canvas), then change the configuration below to locate your other sheets.
Also, since the schema (columns) are not all the same, whats the best option?
I do want the columns that are the same within the sheets to be joined
I'm attaching a workflow here. Please have a look.
So I've an excel, having 2 sheets 'Trans' & 'Acc'
Both sheets have a common column named 'Trans'.
I joined both the sheets by 'Trans' column. If this is what you're looking for, you can download the workflow and check 😃
Excel Inputs:
What if there is another sheet that doesnt have a common column, can I still those sheets columnsas well ?
I don't think we can normally join it.
But we can think of a workaround.
1. Pull out all the sheet data using dynamic Input.
2. Give a Record ID for each sheet data.
3. Join it using Record ID. (either use Join Tool or Join Multiple) While Joining, you can uncheck the Record ID.
Just give it a try. I hope this would work.
There might be a better workaround for this. I'm also open for any suggestion. 😀
I've added a new sheet having no common column .
