Challenge #466: Organize Oscar Winner Data

[Yoshiro_Fujimori]

My solution.

Spoiler

"Original Tab" was truncated to the initial from the full sheet name.
"Rating" column in original sheets was renamed to "Rank".
(This kind of confusion happens in the real world. So not a big issue anyway.)

Macro

Workflow

Formula
FileName =
  Replace([FileName2], "List of sheet names.xlsx|||`Sheet1$`", "") 
  + "Movie Inventory Challenge1.xlsx|||`" 
  + [Sheet Names] 
  + "`"
SheetName = REGEX_Replace([FileName], ".*`(.*)`", "$1")
Original Tab = Left([Original Tab], 1)

[Tofel]

Spoiler

 

 

[lisahitch95]

Nice little trap with Rating and Rank!

Spoiler

  Workflow that incorporates Macro

Macro to import sheets

[balajilolla2]

Spoiler

Solution Attached

[AaronSmatta]

Data was still messy coming through the macro, but was still able to solve the challenge.

[yoiikotu]

Spoiler

Flow:

Batch Macro:
  
In case batch macro is missing an input anchor: 

 

what a challenge! -first time completing a macro challenge whew!

[tammybrown_tds]

This took me way too long to get the macro to work.  Literally 4+ hours.  Goodness.

Spoiler

[ahsanaali]

Spoiler

Batch macro to read the file

 

[nSpire]

Spoiler

 

[RWvanLeeuwen]

Ah yes, the bugs.. Another workflow with bugs, but this time the control parameter does not result in an input anchor (see attached).

How I stopped worrying and learned to love the bugs:

Spoiler

2024.2 user version cannot work with batch macros? anyways - I dont have time to file a bug report and do more testing