Help with formula (or possible macro?)

[winds]

Hello. So recently I had a bunch of spreadsheets in separate files, and I grouped them all together back by coding the individual files using numbers (1,2,3 and etc.), copying one set of data by having one spreadsheet opened (say spreadsheet 1 into the 1st column of my master spreadsheet), then dragging that data in the master to match the number of files I wanted (about 30+), then manually find and replace each column so that I change the spreadsheet in the formula (eg. 1 to 2 in 2nd column, 1 to 3 in 3rd column). Now this was about 30+ files so it was not too bad to do manually. But in the future I may have to do the same task about a 100+ files and that would be a bit overkill to be honest. Is there some magic formula or macro I can do to get around this task more easier?

 

[winds]

Just to update the thread, I read that power query could help but I think that's only for users with Microsoft 365.. if I'm not mistaken... does that mean a vba macro is necessary?

 

[jtakw]

Hi,

Power Query, free for 2010 and newer, Built-in for 2016 and newer.

See here:

What is Power Query? - Power Query

Learn what Power Query is, and how it can help you get and transform data

powerquery.microsoft.com

 

[Fluff]

Built-in for 2016 and newer.

But not on a Mac, only 365 on a Mac has PQ

 

[jtakw]

But not on a Mac, only 365 on a Mac has PQ

Thanks, I wasn't even thinking about Mac, just so used to thinking PC.

 

[Fluff]

I wasn't even thinking about Mac, just so used to thinking PC.

Snap.

 

[winds]

Yes so I think power query is not an option for me since I use Mac... does that mean I need a macro?

 

[Fluff]

does that mean I need a macro?

Very probably. But as I do not have a Mac & so know nothing about the path structures I cannot help.

 

[winds]

Very probably. But as I do not have a Mac & so know nothing about the path structures I cannot help.

I think as long as I have a VBA code it should work. I usually just play around VBA's whenever I need taken from others and altering it slightly to suit my needs tbh as long as I have the basic vba code... I don't know how to build one from scratch though.

Having said that... anyone willing to craft a vba code for this kind of task?

 

[winds]

Sorry, just bumping this thread to see if anyone willing to help construct a macro for this menial task..