Excel macro to shift columns data

I asked an almost identical question earlier today but I made a mistake in my question and I'm trying to figure out how to modify the code I was given by MatthewsPatrick, one of the experts on here.

I have an Excel spreadsheet with 8 columns.  It looks like this:

(Col A)      (Coumn B)       (Column C)   (Column D)    (Column E)     (ColumnF)    (ColumnG)   (ColumnH)
    Date      Store Number   First Name1  Last Name1  First Name2   Last Name2  FirstName3  LastName3
1/1/2007           1                   John              Smith             Steve            Johnson       Diane         Beck
1/1/2007           2                    Ed                 Wood                    
1/1/2007           3                    Bob               Marley           Bill                Clinton

The number of employees for a given Store Number could be anywhere from 1 to 3.  I need to shift all employees names into 1 column (creating additional rows as a result if there is more then 1 employee for a given store number).  So, using the example above, my expected results would be:

    Date         Store Number       Last Name             First Name      
1/1/2007              1                    John                      Smith            
1/1/2007              1                    Steve                    Johnson
1/1/2007              1                    Diane                     Beck
1/1/2007              2                    Ed                          Wood                    
1/1/2007              3                    Bob                        Marley              
1/1/2007              3                    Bill                          Clinton

In my original question, I had the first and last names appearing in 1 column. This is wrong. The first and last names are in seperate columns. So, the modification to the code I need is to look in the Last Name columns of each row (columns D, F, H).  If there is no value in column F or H, no row is to be added. If there is a value in column F, add a row and move the contents of columns E and F (first and last name) to the new row and keep the relationship with the State and Store Numbers the same. Do the same procedure for the first and last names in columns G and H.  I want to end up with a worksheet with 4 columns, namely Date, Store Number, First Name,  Last Name. In other words, all first names are appear in 1 column and all last names are to appear in 1 column. The only difference in my previous question is that the first and last names appear in seperate columns in my source file. I originally indicated that first and last name were combined in 1 column. This is wrong. Can anybody help me modify MatthewPatrick's code?  I'll post his code in a sec.

Start Free Trial