Skip to main content
Solved

Creating separate Excel files based on unique values in a master excel document

  • November 11, 2024
  • 5 replies
  • 45 views
L
Forum|alt.badge.img+1
  • Lhos
  • Cadet | Tier 2
  • 2 replies

I have a workbook that contains data for multiple thousands of employees in multiple countries.  I need to separate that data into individual workbooks that contain just the rows for the employees that correspond to each country (So a “USA” workbook for all USA employees, an “India” workbook for all India employees, etc.)  In the end, if I have employees in 20 countries, I need to have saved 20 separate workbooks.    

I’ve opened my workbook, and logically I believe I need to (First) retrieve the value from the country column, (second) if no workbook exists for that country, create one, add the headers and row of data, and save it with the name of the country, (third) if the workbook does already exist, append the row to the existing workbook

 

I am having a hard time finding the right actions to do this in my loop.  

 

Best answer by Aaron.Gleason

Your automation would first open the workbook in read/write mode. If your worksheet has headers, make sure the “Contains headers” is checked at the Excel Advanced: Open action.

Then use a Loop: Excel Advanced - For each row in worksheet. I’m assuming you have a column header for the country.

One way to check to see if that sheet exists is to use a cell outside of your data (e.g., if you have 5 columns of data, use a cell in column 7, and make sure not to use row 1 which is a header row -- for our example, let’s say cell H2) and use that as a scratch cell. What will you put there? A simple formula to detect if the sheet exists.    =sheet(“$mySheetName$”)    (Use the Excel Advanced: Set Formula action, and be sure to put quotes around the variable for the sheet name variable.)

Then read that cell back in with an Excel Advanced: Get Cell action. If the value returned is #N/A, then you know the sheet doesn’t exist and you should use logic to create a sheet and set headers. If it returns a number, that is the numeric index for the sheet (e.g., the first sheet reports “1”). That means you can go directly to the sheet with that index and put your data in.

How do you put your data into each sheet? I would recommend using the Set Cell/Go to Cell combination based on the active cell. That way you won’t have to keep track of the position you’re writing to in each sheet.

Interestingly, this is VERY similar to one of our Master Certification use cases several years back. Good luck!

View original
Did this topic help answer your question?

5 replies

Aaron.Gleason
Automation Anywhere Team
Forum|alt.badge.img+10
  • Aaron.Gleason
  • Automation Anywhere Team
  • 504 replies
  • Answer
  • November 11, 2024

Your automation would first open the workbook in read/write mode. If your worksheet has headers, make sure the “Contains headers” is checked at the Excel Advanced: Open action.

Then use a Loop: Excel Advanced - For each row in worksheet. I’m assuming you have a column header for the country.

One way to check to see if that sheet exists is to use a cell outside of your data (e.g., if you have 5 columns of data, use a cell in column 7, and make sure not to use row 1 which is a header row -- for our example, let’s say cell H2) and use that as a scratch cell. What will you put there? A simple formula to detect if the sheet exists.    =sheet(“$mySheetName$”)    (Use the Excel Advanced: Set Formula action, and be sure to put quotes around the variable for the sheet name variable.)

Then read that cell back in with an Excel Advanced: Get Cell action. If the value returned is #N/A, then you know the sheet doesn’t exist and you should use logic to create a sheet and set headers. If it returns a number, that is the numeric index for the sheet (e.g., the first sheet reports “1”). That means you can go directly to the sheet with that index and put your data in.

How do you put your data into each sheet? I would recommend using the Set Cell/Go to Cell combination based on the active cell. That way you won’t have to keep track of the position you’re writing to in each sheet.

Interestingly, this is VERY similar to one of our Master Certification use cases several years back. Good luck!

Vatsy
1 person likes this
  • Vatsy
    Vatsy
L
Forum|alt.badge.img+1
  • Lhos
  • Author
  • Cadet | Tier 2
  • 2 replies
  • November 11, 2024

Thank you for your reply.  I’m heartened that this has been tackled in the past, but also worried that I’m just beginning to use Automation Anywhere and this is supposed to be my “basic bot” project and not a Master Certification.  

I understand the basics of the solution you laid out, but I don’t understand all the steps I’ll need add to accomplish this after the “I have opened the workbook” step in the task bot; the actions/selections in the TaskBot tool are not intuitive, to me.  

Shouldn’t I be able to create a blank “unique country” list variable, and then set up a loop to read the country for each employee, compare it to the “unique country” list variable, and use an IF command that basically says, “If this country exists on my list, open the appropriate file and add this record to it, otherwise create a new file for that country and add this record to it?”

 

 

Vatsy
1 person likes this
  • Vatsy
    Vatsy
Aaron.Gleason
Automation Anywhere Team
Forum|alt.badge.img+10
  • Aaron.Gleason
  • Automation Anywhere Team
  • 504 replies
  • November 11, 2024

Yes you can but that complicates the automation. This is too complex for a "basic bot". You need a good grasp of the Excel Advanced package, along with the Set Formula, Set Cell, Get Cell, Go to Cell, Open, Save, Close, Create Workbook actions; the Loop package; using Record variables; and much more. 

I would start your learning with something much simpler. This sounds like a commercial use of our community edition software that is not legally allowed for commercial use. It's for learning only. 

Vatsy
1 person likes this
  • Vatsy
    Vatsy
L
Forum|alt.badge.img+1
  • Lhos
  • Author
  • Cadet | Tier 2
  • 2 replies
  • November 12, 2024

Thank you also for your reply.  I am currently in an Automation Anywhere class, but my company is sponsoring me; I am using the Community edition.  Apologies if this is in the wrong place. 

Vatsy
1 person likes this
  • Vatsy
    Vatsy
Aaron.Gleason
Automation Anywhere Team
Forum|alt.badge.img+10
  • Aaron.Gleason
  • Automation Anywhere Team
  • 504 replies
  • November 12, 2024

No, you’re in the right place.   :)

But my earlier comment stands: This is not a beginner bot. This is very similar (actually slightly harder!) than one of our Master Certification use cases. I could build it quickly because I’ve had over 6 years experience using this software, but for someone just starting out, there is a lot more here than it looks.

Some topics to look at are using the relative cell positioning options for the “Go to Cell” and “Set Cell” actions. They will greatly simplify your automation. This allows you to move the “cursor” (highlighted cell) in Excel rather than using absolute coordinates (e.g., A1). My comment about using the =sheet() function, on the other hand, requires use of absolute coordinates.

Vatsy
1 person likes this
  • Vatsy
    Vatsy

Reply


Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings