Tricky issue, Merging like named columns

[aallaman]

Hi All!

I am stumped by this one.

My data set has about 240 columns of metrics but is should only have 190. This is because the extra 50 columns are a result of some given metric having slight variations in naming convention. For example, there is a field that should be named Video 1 - Complete, but when the tracking was set up some genius used three different naming conventions for the same field depending on the ad unit:
Video 1 - Complete
Video1 - Complete
Video 1: Complete

This translated into one field being reported in multiple columns. I have about 40 metrics affected.

I can somewhat easily change the duplicated fields to a common name, but when I pivot Excel does not combine the data into one field. I get:
Video 1 - Complete
Video 1 - Complete2
Video 1 - Complete3

So I figure that I need to merge these fields in the dataset before pivoting. I have done this kinda stuff using Access queries, but unfortunately Access is not available at my work.

Is there a way to merge fields in Excel? How about a way to tell excel to look over the data set, then spit out on a new tab a revised dataset that has identically named field combined?

I'm pulling my hair out trying to do this manually.

Thanks for any suggestions!!!!

Allen

 

[Derek Brown]

It is difficult to provide a solution as I have no 'view' of the data structure.
However, perhaps you could use SUBSTITUTE to convert them at an earlier stage?
For example, if A2 to A4 contains the three examples that you gave, the formula in B2 would be:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"Video ","Video"),"1:","1 -"),"Video1","Video 1")