VBA needed to find duplicate headings and concatenate the columns

[jufglanville]

Hi everyone,

I'm looking for a way to use vba to search the headings of a table of data and if they are the same then concatenate, copy and paste the new 'merged' column to another sheet. I think I should probably state that I am very new to vba and have been teaching myself the last few weeks.

I get through sheets of data from many different sources and as a result this data is not uniform. To try and combat this I have created a button that applies a dropdown list to all the headers of the table of data. There are about 20 different options to choose from. Once selected I want another vba macro to look through the headers and see if any are the same, if they are, then I want to concatenate the data separating it with a ', ' and copy and place to a new sheet.

Sometimes there are multiple headers with the same value and so concatenation will be needed, sometimes there is only a single header of a certain value and in this case the columns simply need to be copied across as is. Also not all of the data is provided all the time and so if no header is found I would need the vba to skip that header title.

The sheet name where the raw data is located is 'FOI Data' and the headers are always in the top row though the number of columns can vary. The paste location sheet name is 'Edited'.

The heading name also determines the cell location of the paste in the 'edited' sheet so:

BA - D1
Rate - E1
AC - F1
Start - G1
Forward - H1
Ac Mail - I1
ForAd - J1
ForPost - K1
16/17 - L1
17/18 - M1
Relief - N1
Empt - O1
Char - P1
Disc - Q1
Mand - R1
Sbrr - S1
Reoc - T1
New build - U1
Rural - V1
Exempt - W1
Retail - X1
Credit - Y1

Any help would be greatly appreciated!!