VB Macro help

[steeld95]

Hello,

I am trying to write a macro for my excel file. I am bringing in data from an xml. The data is a list a people and some categories that relate to each person. Each person can be linked to multiple categories, so each person is linked on mutlple lines. One column has a category name and the column next to it has a category value. The file can have 5 category names, and they are the same and repeat for each person. The category value is associted with the category name, but they can be different for each person. I need to transpose the category name to accross the top row as a header and the correlating category value listed below each category name, while removing the duplicate rows for each person. Below is an example of the view of the data when I first bring it in the Excel from the XML. Below that, is how I want it to look after I run the Macro. Any help would be greatly appreciated!

How data displays after brought in from xml

Person SSN Category Name Category Value

Smith 111-11-1111 Location Main Office
Smith 111-11-1111 Type Full time
Smith 111-11-1111 Role Executive
Jones 121-11-1111 Location Satelite Office
Jones 121-11-1111 Type Part time
Jones 121-11-1111 Role Assistant


Below is how I want it to look after using the macro

Person SSN Location Type Role

Smith 111-11-1111 Main Office Full Time Executive
Jones 121-11-1111 Satelite Office Part time Assistant

 

[pboltonchina]

Try this on a copy of your data

Sub Consolidate()
'JBeaucaire  (9/18/2009)
'Columnar data is Sorted/Matched by column A values, merge all other cells into row format
Dim LastRow As Long, NextCol As Long
Dim LastCol As Long, Rw As Long, Cnt As Long
Dim DelRNG As Range
Application.ScreenUpdating = False

'Sort data
    LastRow = Range("A" & Rows.count).End(xlUp).Row
    Range("A1").CurrentRegion.Sort key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
    
'Seed the delete range
    Set DelRNG = Range("A" & LastRow + 10)
    
'Group matching names
    For Rw = LastRow To 2 Step -1
        If Cells(Rw, "A").Value = Cells(Rw - 1, "A").Value Then
            Range(Cells(Rw, "B"), Cells(Rw, Columns.count).End(xlToLeft)).Copy _
                Cells(Rw - 1, Columns.count).End(xlToLeft).Offset(0, 1)
            Set DelRNG = Union(DelRNG, Range("A" & Rw))
        End If
    Next Rw

'Delete unneeded rows all at once
    DelRNG.EntireRow.Delete (xlShiftUp)
    Set DelRNG = Nothing

'Add titles
    NextCol = Cells(1, Columns.count).End(xlToLeft).Column + 1
    LastCol = Cells(1, 1).CurrentRegion.Columns.count
    Range("B1", Cells(1, NextCol - 1)).Copy Range(Cells(1, NextCol), Cells(1, LastCol))

Cells.Columns.AutoFit
Application.ScreenUpdating = True
End Sub

 

[steeld95]

Thanks alot for your help! I am getting a run time error '1004'. It says the information can not be pasted because the copy area and the paste area are not the same size and shape. when I hit the debug, it is showing me this section of the Code highlighted (Red Below):

or Rw = LastRow To 2 Step -1
If Cells(Rw, "A").Value = Cells(Rw - 1, "A").Value Then
Range(Cells(Rw, "B"), Cells(Rw, Columns.Count).End(xlToLeft)).Copy _
Cells(Rw - 1, Columns.Count).End(xlToLeft).Offset(0, 1)
Set DelRNG = Union(DelRNG, Range("A" & Rw))
End If

The sheet I am trying to run it on has 158 rows. Each time I run it the amount of rows may be different, depending on the xml.

Do I have to update the code depending on the amount of rows? Thanks again for your help! I am very new to this, so I really appreciate it!

 

[pboltonchina]

Do you have any merged cells in the copy or destination range?

 

[Rick Rothstein]

Give this macro a try...

Sub PutOnOneRow()
  Dim X As Long, Start As Long, LastRow As Long
  Const StartRow As Long = 1
  Application.ScreenUpdating = False
  Columns("C").Delete
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Start = StartRow
  For X = StartRow + 1 To LastRow + 1
    If Cells(X, "A").Value & Cells(X, "B").Value <> Cells(X - 1, "A").Value & Cells(X - 1, "B").Value Then
      Cells(Start, "D").Resize(, X - Start - 1) = WorksheetFunction.Transpose(Cells(Start + 1, "C").Resize(X - Start - 1))
      Start = X
    End If
  Next
  On Error Resume Next
  Columns("D").SpecialCells(xlBlanks).EntireRow.Delete
  Application.ScreenUpdating = True
End Sub

Note: I wasn't sure where your data started at, so I added a constant (the Const statement) where you can set it. I used Row 1 as my guess.

 

[steeld95]

When I run this one, it deletes the 'Category Name' column, which I need it to do, but it doesn't transpose the values along the top header row.

I probably need to give a little more information on what I am trying to do.
I am trying to take xml's and put them into Excel and format them correctly before I pass it on. Each xml that i have to covnert is for a different client, so the data can be different. Depending on the client, I send them a different amount of data elements. Also, the header columns can be in a different order. However, most of the sheets have the 'Category Name' and ' 'Category Value' columns. This is the column I am having the most trouble with. Whenever these columns are included (which is most of the time), I need to transpose the category names to the top column as headers, with the category value in the row below the category name. Can I base the macro off of the header in the top column?

Example:

This is what it looks like when i initially pull in the xml data to Excel.

First Name Last Name Id # Category Name Category Value

Jim Smith 12 Location NJ
Jim Smith 12 Role Executive
Jim Smith 12 Department Legal
Jenn Jones 55 Location SC
Jenn Jones 55 Role Management
Jenn Jones 55 Department Claims


When I get this, I want to run a macro that will get rid of the duplicate rows, transpose the category name data to the top header row, and place the correlating in the 'category value' field directly under the correlating category name. Below is the outcome I would like after the macro is run.

First Name Last Name ID # Location Role Department

Jim SMith 12 NJ Executive Legal
Jenn Jones 55 SC Managment Claims


One other thing, each xml can have a different number of categories. Would i be able to use the same macro for each xml, or will I have to edit it depending on the number of categories in the file? Thanks again for all your help! I really appreciate it!

 

[Rick Rothstein]

Sorry, I misunderstood exactly what you wanted. The following should work for you (for any of your XML data files)...

Sub PutOnOneRow()
  Dim X As Long, Start As Long, LastRow As Long
  Application.ScreenUpdating = False
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Start = 2
  For X = 3 To LastRow + 1
    If Cells(X, "A").Value & Cells(X, "B").Value <> Cells(X - 1, "A").Value & Cells(X - 1, "B").Value Then
      Cells(Start, "E").Resize(, X - Start - 1) = WorksheetFunction.Transpose(Cells(Start + 1, "D").Resize(X - Start - 1))
      If Start = 2 Then
        Range("D1").Resize(, X - 2) = WorksheetFunction.Transpose(Range("C2:C" & (X - 1)))
      End If
      Start = X
    End If
  Next
  Columns("C").Delete
  On Error Resume Next
  Range("D" & 2 & ":D" & LastRow).SpecialCells(xlBlanks).EntireRow.Delete
  Application.ScreenUpdating = True
End Sub

 

[steeld95]

This last formula is close to what I need. The file can differ on size, so sometimes it can be a file of up to 100 lines, or more. Per the example I gave above, a person can be dupliated on the list. Is there anyway to remove the duplicate people and only have 1 row for person while transposing the other columns specified? Also, not every person on the list will have a category, the code above breaks in that case. Would it be possible to code for that? Basically if the person doesnt have a category, it just wouldn't have to worry about transposing the category name and category value columns.

And lastly, depending on the file, there could be additional columns, so the category name and category value columns, which I need transposed, won't always be in the 3rd and 4th column. Will I just have to adjust the macro each time to point to the column where the category name and values are?


Thanks again for all the help! This has been extremely helpful and it is very close to what I need!

 

[Rick Rothstein]

One problem I see where my code may not be working for you is that you changed what the structure of your data is. In your initial posting, you said there was a single "Person" column for the names, now you are showing two columns for them (First Name, Last Name)... that, of course, throws off completely where my code is looking for its data. You have also indicated the Category Name and Category Value columns can be located anywhere. So, before trying to patch my code for you, I need you do lock some things down. Please answer all of these questions...

1) How many "name" columns are there and are they always the first column(s) on the sheet?

2) Is the ID# always the next column?

3) Is there a header row and, if so, do the "Category Name" and "Category Value" columns always have exact the text header that I quoted?

4) You are now saying there are other columns of information on the sheet... my code assumed you wanted to output only the condensed list for your 4 originally posted (now 5?) data columns and have this output replace the existing data. So, what is to be done with the extra data columns... delete them? Or do you want the output list to go to another sheet perhaps (and not modify any of your original data)?

 

[steeld95]

Sorry for the confusion, my requirements kind of changed in the middle of this one. Based on the new requirements, here is what I am looking at. I have a standard xml that I used for many clients. Based on the client, that decides which columns I need to pull into the excel spreadsheet. So, for some clients, i might have to pull 4 column onto the sheet, but for others I have to pull 8. SSN will be one column I pull for all clients, it will be on every spreadsheet. Here are the answers to your questions.

1) the name columns will not always be the first column. It basically depends on the client. We pull first and last name for most clients. SSN is the category we pull for all of them though.

2) The id is not always the next column.

3). Yes, there is a header row. Yes, the 'Category Name' and 'Category Value' always have the exact text header. This is the same for all clients. Also, the 'Category Value' column will always be directly after the 'Category Name' column. Since this will always be the same, can the macro be build to transpose based on the name of these columns?

4) Yes, there can be a different amount of columns, based on the client. There will most likely always be duplicate rows. The amount of duplicate rows can change per client. Basically, if one client has 5 category name and 5 category value rows, the person will be listed 5 times. I would like the macro to delete the duplicates and only have each person listed once. The SSN can be used to detect duplicates.

One other thing to throw into it, there can be a person listed that does not have any categories. There should not be that many in each sheet. So, a excel sheet can have 5 rows for a person because they have 5 categories, but a person with no categories will only be listed once. So, you would have to do any transposing for those people.


So basically, I would like to transponse the data ' Category Name' column to the header row, and the data in the 'Category Value' column directly underneath the correlating header from the category name.


I know this is really complicated, I really appreciate all your help. This would be a huge huge help. Thanks alot!