if data from predefined list does not exist in row, add it to the row

Hi all, first time user! My eyes fee like they are falling out and I'd appreciate your help!

I am working with 18 databases (1 per year x 18 yrs) and I need to make a standardized header row for each of them. The goal is to merge all 18 databases together in another program. The variables I am working with are very inconsistent from year to year. Some have been recoded for more consistency but many variables are only available for one year. So, what I am doing is creating placeholders for those odd variables in the years they are unavailable. That way they will contain values if the label is applicable for that entry and will be blank if the label is not applicable. I want all 18 years to have the exact same variable labels.

So I have the master list of variable labels. When my header row for any year's database is incomplete, I need to draw from the master list to make sure every label is represented. I only want to draw from the master list if a particular value isn't in the header row.

I originally tried to just append the entire master list to the tail end of the header row for every year, then sort and delete duplicates. The databases are very large and excel can't handle these functions very quickly...and I will lose my mind if I have to do this 18 times.

Right now I have the master list in "sheet 1" A1:A205
Actual data is in another sheet in same workbook, labeled by year i.e. "2001"

It would be extremely awesome if I could also get the macro to maintain alphabetical order in the header row but at this time it looks like the sheet will sort the columns, although slowly...

Anyone have any ideas for me? I'm sure this a simpler issue than I'm anticipating but I'm at the end of my rope trying to figure it out!!!!

Hi

Are the labels in the range A1:A205 in the order that you want them in the data sheets? So that the heading that is in A100 will be in column 100, A205 in column 205 etc?

rylo

Hi Rylo, Yes that is exactly right. In case it matters they are also alphabetized.
THX! -T

Hi

OK, next question. Will the existing data in the data sheets (2001, 2002 etc) already be in the right order except for the missing columns? So if the headings should be H1, H2, H3 .... but H2 is missing, will the order be H1, H3.... Or can the columns in the data sheets be in any order?

rylo

Hi again, Yes I'll have the columns in order (as much as they can be) as well. I figured originally that the easiest way to get this together would be to just alphabetize everything, so the master list of variable labels and the columns in the database are currently arranged that way--my main reason for ordering this way was so that it would be easy to see any mistakes. To use your example it would be most optimal to go from a header row of H1, H3, H5 to a header row of H1, H2, H3, H4, H5.
Thanks again--hope this answers your question!

...but I will take an H1 , H3, H5, H2, H4 lineup as well! Any ideas, friendly folks?

Hi

Try this.

Please Login or Register  to view this content.

rylo

Thanks for the help! This almost works--I've set up a dummy sheet to test it. Here's what happened:

Right now I have my master list in Sheet1. It's just a list of letters, A-Z, in column A.
In the second sheet I have the following letters in individual cells in row 1: A, H, I, U, R ...each of these columns also contains dummy data.

When I run the script it leaves the dummy variable A as-is, then inserts columns B-Z from the master list. Following Z are the remaining dummy variables/data. Variable A is only listed once but H, I, U, and R are listed twice.

Soooo close to what I need it to do! Any way to get it to only add the variables that aren't already in the row?

THANKS SO MUCH!

Hi

Just so we are working on the same thing, can you please attach your dummy file to the post.

rylo

Sure thing, here you go. Thanks again! -T

Hi

2 things.
1) 2 of the headings in year have got a trailing space. Remove them so that the headings exactly match the values in sheet1
2) the headings in year are not in alphabetical order, and in post #5 you advised they would be in order.

So make those 2 changes, and try the code again.

rylo

YES! YES!

I wish I could pay you. It works perfectly. Thanks so much!!!!!