Faster way than deleting columns one by one to reduce my data to the correct columns

Hello,

Every week I take a large spreadsheet (~90 columns) and break that into smaller spreadsheets (~15 columns) manually. The smaller spreadsheets are subsets of the larger sheet but columns are in a specific order and sometimes not like the sequence of the large sheet. I typically just go in and manually copy all the columns into a new spreadsheet with all the accompanying row data, then delete the columns I don't need. But this takes forever!

Is there a way to pull only the required columns and data into my reoccurring smaller sheets?

For example:
The large spreadsheet has columns:
ABCDEFG

The smaller sheet has columns in this order:
ACF
or even
FBG

I created templates for each smaller reoccurring spreadsheet (just with the columns and no data) so I know which columns to bring over. I tried using a VLOOKUP formula but I have to know the col_index_num and that changes for each smaller sheet.

I am sure many of you have been in this same instance whereby you want to pare down a huge file into a smaller one but manually deleting or copy/pasting only those columns is tedious.

Many thanks!

Do you have headers in the main sheet and the subsidiary sheets? If so, you could put this formula in A2 of each subsidiary sheet:

=INDEX('Main sheet'!$A:$AD,ROW(),MATCH(A$1,'Main sheet'!$1:$1,0))

then you can copy this across as far as you need for each sheet, and then copy the formulae on row 2 down as far as you need (until you get blanks or zeros).

Hope this helps.

Pete

Pete,

I've tried that in the attached file but no luck. Can you help me understand the issue and input the correct formula in the ORDER NUMBER (A2) field?

test.xlsx

You didn't copy the formula directly - this is the formula that you should have in A2 of the Small sheet:

=INDEX(Large!$A$1:$H$999,ROW(),MATCH(A$1,Large!$1:$1,0))

then you can copy this across and down as required. Excel will return an empty cell as a zero when using formulae like this - if you want to avoid that happening then you can use this formula in A2 instead:

=IF(INDEX(Large!$A$1:$H$999,ROW(),MATCH(A$1,Large!$1:$1,0))="","",INDEX(Large!$A$1:$H$999,ROW(),MATCH(A$1,Large!$1:$1,0)))

then copy that across or down.

Hope this helps.

Pete

AMAZING! Thanks Paul. I have never used the INDEX function nor do I understand the ROW() instead of using a value. But I will read more about this, because this is very helpful.

I'm Pete, not Paul, but thanks for feeding back.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Pete,

Thanks for your help but I have a small problem. The issue is similar but I can't get the formula to work in a similar instance. I guess I don't truly understand the syntax/application of the formula.

I am using a Google Form Doc now to take in some information, unfortunately Google Forms likes to keep the columns in the EXACT order I created the form in so all these responses (new data) are in different column orders that I would need to analyze the data (in the Master). So the issue is I have new incoming data (daily) and would like to put it all in order into the Master file I created. Instead of manually moving all the columns each day around to get the right order of the data I just want to use a similar formula.

Master:
Column names: Timestamp, Name, Email...etc.

New Data output from Google:
Column names: Time, Email, Name...etc.

I have attached a copy of the issue with each tab putting in just a sample of the header names. Cheers

Pete

This would work ok if I only had like 3 or 4 columns but I have many dozens to sort through and pick the right cells to input that formula. Also the Master list columns are in very different order than the NEW DATA so it makes it very difficult to try and match these up initially. I just put those columns in as an example, but there are lots more. Since the headings of the columns are the same in the MASTER list vs. the New Data (just in different order) can I use an =INDEX() similar to before?

I can only go off the file(s) that you attach - they should be representative of your real files, even if the data is made up. Post another example with data in it, then I might understand your problem better.

Pete

Pete,

Thanks for your help; here is a new document with more fields (only a sample) but more to work with

Justin
Master2-New Example.xlsx

Put this formula in cell A5 of the Master sheet:

=IFERROR(INDEX('New Data'!$A$1:$O$999,ROWS($1:2),MATCH(A$1,'New Data'!$1:$1,0)),"")

then copy across to M5. Then copy that row of formulae down until you start to get rows of zeros. Note that the headings have to be the same in both sheets - you get blanks in column L as the heading is different.

Hope this helps.

Pete

WOW you did it again Pete!