Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Weekly Challenges

Solve the challenge, share your solution and summit the ranks of our Community!

Also available in | Français | Português | Español | 日本語
IDEAS WANTED

Want to get involved? We're always looking for ideas and content for Weekly Challenges.

SUBMIT YOUR IDEA

Challenge #118: Think Like a CSE - A not-so-wild-wildcard input!

jessle
8 - Asteroid

Glad to have this one out the way!

Thanks to @OllieClarke for the help, and @NicoleJohnson's solution, which helped me create the super-condensed Version 2 (and taught me a neat little trick about the Dynamic Rename: if the multiple Excel files you're reading in have a single sheet with different names, the Dynamic Rename will still read them all in.)

JethroChen
10 - Fireball
Spoiler
challenge_118_jc.PNG
AkimasaKajitani
17 - Castor
17 - Castor

My solution.

 

Spoiler
I recommend batch macro to read the files that have different scheme. 
AkimasaKajitani_0-1601566786478.png


Macro is 2 step.
Step 1 is to read sheet names.
Step 2 is to read Contents of file by Sheet Names.
Step1

AkimasaKajitani_1-1601566819103.png

Step 2

AkimasaKajitani_3-1601566929267.png

 


 

JP_SDAK
8 - Asteroid

My solution seems to be similar to most others.  I used a directory tool to get list of filenames with wildcard file?.xlsx then passed those to a  batch macro which reads in filename+sheetname and returns all spreadsheet data including filename, sheetname appended at the end.

 

Spoiler
JP_SDAK_0-1610324592114.pngJP_SDAK_1-1610324622876.png

 

 

rcarog
8 - Asteroid

Solution attached.

mpennington
11 - Bolide

Attached.

 

Spoiler
MP 118b.jpg
kelsey_kincaid
12 - Quasar

A good refresher in Batch Macros!

 

Workflow:

Spoiler
kelsey_kincaid_0-1619148755707.png

 

Macro:

Spoiler
kelsey_kincaid_1-1619148785928.png

 

apathetichell
18 - Pollux

complete with typo in the filename....

Sashikumar
8 - Asteroid

My Solution.

Spoiler
ch118.PNGch118_1.PNG
phottovy
13 - Pulsar
13 - Pulsar

Gotta love spreadsheet without actual column names. Not confusing at all!