To see less ads Register or Login ----- Daily Fantasy Sports games 18+

How to make column headings across multi workbooks the same

A forum to discuss or share knowledge of spreadsheets.
Post Reply
User avatar
Jonathan
FISO Jedi Knight
Posts: 21212
Joined: 13 Oct 2005, 18:42
Location: ^ That's my dad! ^ KGC Captain's Drive-in 2015 Champ
FS Record: OFL Champions League Sprint finalist 10/11

How to make column headings across multi workbooks the same

Post by Jonathan »

Hello all.

I am trying to streamline something at work, and am having to use data supplied by our client. The problem is, their inconsistencies in how they name things is breaking my plan to use INDEX and MATCH functions.

For example.
Master workbook has headings across the top:
Top Layer > Product Code > Origin

But I have 400+ product workbooks which *should* have those same column headings (but as rows), but they are slightly different in a lot of cases, so the functions are failing ie TopLayer, Top Layer, Top layer, Product code ProductCode....) you get my drift.

What is the best automated way to make sure that all of the individual product workbooks have their row headings matching exactly to the column headings on the master workbook, so the correct data is returned.

Thanks all.

User avatar
jeffmcgow
Dumbledore
Posts: 7624
Joined: 13 Oct 2005, 18:44
FS Record: FPL - FISO Mirror League 1st 2015-16 | Sun - Cheap as Chips Conference 1st 2014-15 & 2015-16

Re: How to make column headings across multi workbooks the same

Post by jeffmcgow »

You could use "Edit Group Mode"

Click on the 1st sheet tab, hold down shift, click on the last. You'll then have all sheets selected.

Then, whatever you type on the 1st sheet overwrites the same cells on all other sheets.

To turn off group mode (when all sheets are selected) click on any tab.

Hopefully that's what you're after. :)

PS I also note that you can right click on a tab and you get the "select all sheets" option. I didn't know that!

User avatar
Jonathan
FISO Jedi Knight
Posts: 21212
Joined: 13 Oct 2005, 18:42
Location: ^ That's my dad! ^ KGC Captain's Drive-in 2015 Champ
FS Record: OFL Champions League Sprint finalist 10/11

Re: How to make column headings across multi workbooks the same

Post by Jonathan »

Oh - well that's something new to remember for the future but unfortunately all of my products are in separate workbooks - approaching 400 of them - not in worksheets within a single workbook.

I had toyed with joining them all together but couldn't then find a way of extracting them back into individual workbooks which is ideally how I want them to be for easier editing.

Thanks for the reply jeff

User avatar
jeffmcgow
Dumbledore
Posts: 7624
Joined: 13 Oct 2005, 18:44
FS Record: FPL - FISO Mirror League 1st 2015-16 | Sun - Cheap as Chips Conference 1st 2014-15 & 2015-16

Re: How to make column headings across multi workbooks the same

Post by jeffmcgow »

Ah, yes. I see now that you said workbooks in your OP. Whoops :oops:

I guess as well as the row headings being non-standard on different sheets they're not always on the same rows across workbooks. Otherwise you'd be able to use the INDEX function with a direct row reference rather than a MATCH to get the row number.

Thinking aloud, but how many variants of the row headings are there? Under you heading "Top Layer" could you have a few rows containing alternative spellings? Then do a MATCH for each of the options, and use the one which doesn't error for the INDEX formula?

E.g.

A B C
1 Top Layer Product Code Origin
2 TopLayer
3 Top layer2

4 =IFERROR(MATCH(a1, range, FALSE) ,0)
5 =IFERROR(MATCH(a2, range, FALSE) ,0)
6 =IFERROR(MATCH(a3, range, FALSE) ,0)

7 = sum(A4:A6)

8 = INDEX(range2, A7, some_column_reference)

I don't think MATCH is case sensitive so there can't be too many variants. (Famous last words! :) )

The data you have been given may be messier than I expect so my idea may be way off what you are looking for.

Edit: actually, I don't think the above will work at all. I think I may be beaten on this one. :(

User avatar
Jonathan
FISO Jedi Knight
Posts: 21212
Joined: 13 Oct 2005, 18:42
Location: ^ That's my dad! ^ KGC Captain's Drive-in 2015 Champ
FS Record: OFL Champions League Sprint finalist 10/11

Re: How to make column headings across multi workbooks the same

Post by Jonathan »

I appreciate all the thought you are putting into this for me Jeff. :)

I kind of think I am hoping for the impossible on this. I'm probably needing to go for purchasing a search and replace macro which can search multiple workbooks - I'm amazed that isn't a default - and look for the differences that way.

Then maybe some kind of IF statement to flag anything I miss with a colour block.

User avatar
jeffmcgow
Dumbledore
Posts: 7624
Joined: 13 Oct 2005, 18:44
FS Record: FPL - FISO Mirror League 1st 2015-16 | Sun - Cheap as Chips Conference 1st 2014-15 & 2015-16

Re: How to make column headings across multi workbooks the same

Post by jeffmcgow »

I've found this: http://excel.tips.net/T005598_Searching ... books.html

Maybe it could form the basis of a global search and replace procedure.

Combine it with

Sub FindReplaceAll()
'PURPOSE: Find & Replace text/values throughout entire workbook
'SOURCE: http://www.TheSpreadsheetGuru.com

Dim sht As Worksheet
Dim fnd As Variant
Dim rplc As Variant

fnd = "April"
rplc = "May"

For Each sht In ActiveWorkbook.Worksheets
sht.Cells.Replace what:=fnd, Replacement:=rplc, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next sht

End Sub


replacing the bit where workbook names are written out

lRow = lRow + 1
.Cells(lRow, 1) = wbk.Name
.Cells(lRow, 2) = wks.Name
.Cells(lRow, 3) = rFound.Address
.Cells(lRow, 4) = rFound.Value

with the search and replace VBA

It's past my bedtime otherwise I'd have a go at bolting the two bits of code together. Hopefully you can crack it Jonathan :)
Last edited by jeffmcgow on 07 Feb 2016, 22:25, edited 1 time in total.

User avatar
Jonathan
FISO Jedi Knight
Posts: 21212
Joined: 13 Oct 2005, 18:42
Location: ^ That's my dad! ^ KGC Captain's Drive-in 2015 Champ
FS Record: OFL Champions League Sprint finalist 10/11

Re: How to make column headings across multi workbooks the same

Post by Jonathan »

I found a few scripts similar Jeff but problem is, I'm using a Mac and there are differences which don't work unfortunately.

View Latest: 1 Day View Your posts
Post Reply

Return to “Spreadsheets”