1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

XLS Macro to move data to columns, delete empty rows, delete duplicate columns

Discussion in 'Business Applications' started by angelostudios, Aug 25, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. angelostudios

    angelostudios Thread Starter

    Joined:
    Aug 25, 2011
    Messages:
    18
    I started out with the two previous posts about macros (post 1, post 2) that move rows into columns, but I'm having a little trouble getting it to work for what I need. First, I'm trying to do a data merge into Word, but in order to get the data merge to work/look the way I want it to, I need the XLS data to be in columns.

    I'm attaching an example file that has one worksheet of the sample data (though my "real" data has about 300 rows) and the other worksheet is an example of the outcome I need.

    Is there anyway I can do what I need? Exporting into another file or another worksheet within this file would be fine.

    Thanks in advance.
     

    Attached Files:

  2. Sponsor

  3. jimbo1178

    jimbo1178

    Joined:
    Aug 25, 2011
    Messages:
    4
    Would it work if all the names were on seperate tabs or do you need all of them on one ?
     
  4. angelostudios

    angelostudios Thread Starter

    Joined:
    Aug 25, 2011
    Messages:
    18
  5. jimbo1178

    jimbo1178

    Joined:
    Aug 25, 2011
    Messages:
    4
    Is there any way I can get the student ID numbers - you can change the names if you would like but if you could post the orignal report with the names hidden i might have a solution. Hopefully it works.
     
  6. angelostudios

    angelostudios Thread Starter

    Joined:
    Aug 25, 2011
    Messages:
    18
    I will attach the original export in just a minute. The only issue I can see with what you're thinking is that this export will be done each month and the student IDs will change assuming that new children are enrolled, and some may leave. The other issue is that in my example attached above, there were only a weeks worth of information, but we will likely have a months worth in actuality (so 20 rows per student give or take). We will need to export the data each month and have the macro "clean up" the data to use with data merge.
     
  7. angelostudios

    angelostudios Thread Starter

    Joined:
    Aug 25, 2011
    Messages:
    18
    Sorry it took so long, I wanted to change the names to protect the students. See attached.
     

    Attached Files:

  8. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,911
    This macro should work assuming that your data layout is the same as the latest workbook posted. The macro sorts by student ID before arranging the data so just make sure to re-sort however you like after finished. The macro also copies the sheet into the same workbook so that the orignal data is preserved

    Code:
    Sub CopyRows()
    
        ActiveSheet.Copy After:=Sheets(Sheets.Count)
        Cells.Sort Key1:=Range("A2"), Order1:=xlDescending
            
        For i = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
          
        Do While Range("A" & i).Value = Range("A" & i - 1).Value
        
        vLastCol = Left(Columns(Cells(i, Columns.Count).End(xlToLeft).Column).Address(0, 0), 2 + (Cells(i, Columns.Count).End(xlToLeft).Column < 27))
        
        
        
        Range("E" & i & ":" & vLastCol & i).Copy Destination:= _
        Cells(i - 1, Cells(i - 1, Columns.Count).End(xlToLeft).Column + 1)
        
        Range(Range(vLastCol & 1).Offset(0, 1).Address, Range(vLastCol & 1).Offset(0, 6).Address).Value = Range("E1:J1").Value
        
        Rows(i).Delete
        
        Loop
        
        Next i
        
    End Sub


    Rollin
     
  9. angelostudios

    angelostudios Thread Starter

    Joined:
    Aug 25, 2011
    Messages:
    18
    Thank you Rollin! It worked great! I appreciate this so very much! It makes my life so much easier. I don't want to ask too much, but is there a way for us to duplicate the header column cells E1-J1 across the top? It would be great if we could change the name to add a sequence like:

    Punch In Date | Punch In Time | Punch In Date 2 | Punch In Time 2 | etc etc

    It doesn't matter if it is done too many times (like do this 30 times across the top) because I only need it for the data merge later.

    Thanks again!!!
     
  10. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,911
    I've updated the code above so copy the latest to see if it does what you want.

    Rollin
     
  11. angelostudios

    angelostudios Thread Starter

    Joined:
    Aug 25, 2011
    Messages:
    18
    Thanks for the updated code. It seemed to add the titles when I used my dummy file (attached above) but when I used the REAL data, it did not work. It may have something to do with converting the almost 1500 rows of data down to 150 rows. I read somewhere that when a loop is so big, you may have to stop and save along the way. Is that right?

    Also, when I look at the times on the spreadsheet they show up like "10:15 AM" but when I put them in my document they show up as 10:15:00 AM. It shows this value when I select the cell and look in the formula bar. Is there away to drop the seconds off?
     
  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1014294