Lining up data in Excel

Microsoft Office 2007 home and student
January 29, 2010 at 07:07:25
Specs: Windows 2007
Lining up data in Excel

I have two sets of data in an excel spreadsheet. Each list contains Date, Time, Weight and a Ticket number. What I need to do is get Excel to run down the ticket numbers and match them up. It needs to move the information attached to that ticket number with it. I would like the list on the left to remain static and the one on the right to move. list looks as follows

Column A Column B Column C Column D
Ticket No Date Time Weight
152003 12/02/10 15:20 2.201
152244 12/02/10 09:17 2.320
152288 13/01/10 02:15 24.10
153399 15/10/09 17:18 17.23

Next to it is the same set of data but in a different order and some ticket numbers are not there. I need to make them line up so I can calulate the weight differences between the lists and find out if any ticket numbers are missing from one list.



See More: Lining up data in Excel

Report •


#1
January 29, 2010 at 08:22:46
First, you have told us what you "want" and what you would "like", but you never really asked for our help. Since we're all volunteers here doing this work for free, it's just a bit more polite to actually ask for assitance with a "Please" and perhaps a "Thank You".

That said...you can't really "move" the data around within the second list itself, but you can copy it to or from someplace else and end up with same result.

In others words you can't go directly from:

2  2
3  1
1  4
4  

to 

2  2
3  
1  1
4  4

without an interim step.

The following code will first move your second list to the right by four columns so it has room to place the rearranged list next to your original one. You can delete the original table later, either manually or by adding a single line of code to the macro

Make sure you try this on a back-up copy of your workbook in case something goes terribly wrong.

Option Explicit
Sub MatchData()
Dim c, nxtRow
'Insert 4 columns to make room
Sheets(1).Columns("E:H").Insert Shift:=xlToRight
 With Sheets(1).Range("I2:L5")
'Look for each Ticket No in Column A
  For nxtRow = 2 To 5
   Set c = .Find(Sheets(1).Range("A" & nxtRow), lookat:=xlWhole)
'If found, place it and its associated data in Columns E:H
    If Not c Is Nothing Then
     Sheets(1).Range("E" & nxtRow) = c
     Sheets(1).Range("F" & nxtRow) = c.Offset(0, 1)
     Sheets(1).Range("G" & nxtRow) = c.Offset(0, 2)
     Sheets(1).Range("H" & nxtRow) = c.Offset(0, 3)
    End If
  Next
 End With
End Sub


Report •

#2
January 29, 2010 at 10:24:49
Thank you very much for your response. Will give it a go now but wanted to post a reply, to apologise for not saying please or thank you. Had typed please help in subject line but I had to delete it before posting and forgot to put it in the body of my message

Report •

#3
January 29, 2010 at 10:33:53
No problem and thanks for responding.

We aren't looking for praise, but it does feel nice to be asked nicely and thanked afterwards.


Report •

Related Solutions

#4
February 1, 2010 at 05:41:48
Hi There,

Thanks very much for your help, it works very well. I have had to amend the code slightly in order to match my sheet. For some reason it keeps breaking when it runs. Sure it is something simple that I am missing but wondered if you could do me a favour and have a look at the below :

Sub MatchData()
Dim c, nxtRow
Application.ScreenUpdating = False
'Insert 4 columns to make room
Sheets(3).Columns("M:U").Insert Shift:=xlToRight
With Sheets(3).Range("V2:AD3000")
'Look for each Ticket No in Column A
For nxtRow = 2 To 3000
Set c = .Find(Sheets(3).Range("I" & nxtRow), lookat:=xlWhole)
'If found, place it and its associated data in Columns E:H
If Not c Is Nothing Then
Sheets(3).Range("M" & nxtRow) = c
Sheets(3).Range("N" & nxtRow) = c.Offset(0, 1)
Sheets(3).Range("O" & nxtRow) = c.Offset(0, 2)
Sheets(3).Range("P" & nxtRow) = c.Offset(0, 3)
Sheets(3).Range("Q" & nxtRow) = c.Offset(0, 4)
Sheets(3).Range("R" & nxtRow) = c.Offset(0, 5)
Sheets(3).Range("S" & nxtRow) = c.Offset(0, 6)
Sheets(3).Range("T" & nxtRow) = c.Offset(0, 7)
Sheets(3).Range("U" & nxtRow) = c.Offset(0, 8)
End If
Next
End With
Application.ScreenUpdating = True
End Sub

Thanks again


Report •

#5
February 1, 2010 at 06:05:02
re: "For some reason it keeps breaking when it runs"

Since I can't see your sheets from where I'm sitting, it might help if you told me what "it keeps breaking" means.

Are you getting VBA related errors?
Is it not doing what you expect it to do within Excel?

That sort of information would help, along with a general discription of your spreadsheet layout, in this case before the code is run, since the code changes it.


Report •

Ask Question