Macro / formula to pull values from cells based on headers

Hi all

I had a recently similar question, and I was wondering if I could get your help again!

I have a range of Excels with one constant value, and the rest varying. So, it looks like:

No. ValueTagABC ValueTagABD2 ValueTagYN ValueTagSubtag
00034 ValueTagABC ValueTagABD2 Y Subtag
00067 ValueTagABC ValueTagABD2 Y Subtag
00085 ValueTagABC ValueTagABD2 N Subtag


No. ValueTagABC ValueTagXXY ValueTag2YN ValueTagSubtagOther
00085 ValueTagABC ValueTagXXY Y SubtagOther
00093 ValueTagABC ValueTagXXY N SubtagOther
00022 ValueTagABC ValueTagXXY N SubtagOther

The No. is the constant field - so I have another Excel with all of these numbers. What I need now is to populate this with all of the corresponding tags.

These tags are split across multiple Excels, so I need to populate the header fields with the various tags, and then their corresponding value. I could get a list of all of the headers and put these into the Excel in the first instance if that would make things easier - but I won't know which column (B, C, D, X, etc.) will contain the actual correct value for each header.

I hope this makes sense! Thank you in advance for any ideas!

Originally Posted by dflak

In the example, you have a duplicate column header: ValueTagABC. How do you want to handle when the values in these columns have different values?

Hi. Sorry I didn't reply earlier. Yeah - there are duplicate valuses across the Excels. What this is, is a dat which splits every 5,000 rows. If one set of 5,000 has certain values, these are populated. If a set doesn't have values, its field will be ignored.

I fixed this one manually, but it wasn't pleasant..