macros in excel 2000

[jackie]

Hi,

I'm doing some research on a large database of 2000 people, and all of them
have completed different questionnaires. everyone has their own id number.
I'm using excel 2000 and am trying to merge the various questionnaires into
one big file and sort the data so that for any given individual (id number)
their responses are on the same row. the problem is that not every
individual has completed every questionnaire, so I'm merging data files of
different sizes and need to then line up the id numbers for the individual
questionnaires so they match up

eg. questionnaire 1:

id. item 1 item 2
101 ... ...
103 ... ...
104 ... ...
106 ... ...

questionnaire 2:
id item 1 item 2
100 ... ...
101 ... ...
102 ... ...
104 ... ...
106 ... ...

I want to merge them so the id numbers that are common to both match up and
the corresponding responses are all on the same line

i.e..
id qu 1 item 1; qu1 item 2; qu2 item 1; qu2 item2
101 ... ... ... ...
104 ... ... ... ...
106 ... ... ... ...

at the moment I've recorded a macro that will delete cells and shift cells
up in one go, but this takes ages does anyone knows a quicker way to sort
the data?

cheers

Jackie

 

[Dave Peterson]

I'd approach it this way:

I'd put all the worksheets into one giant book.
Then I'd insert a new worksheet that will have a list of each person who took
any of the surveys.
Then I'd use a bunch of =vlookup()'s to return the values to the correct
columns.

Then convert all my formulas to values and wipe out any of the #n/a error values
(missing) with an Edit|Replace (all).

If you think that this might work and after you put all the worksheets into that
giant workbook, a quick way to get a unique list of names.

Insert a new sheet.
Copy A1:A999 of the first sheet to A1 of the newsheet (include one header row)
copy A2:A999 of the 2nd through last sheet (exclude the header row) after the
last used cell in column A of that new worksheet.

Now follow the instructions at Debra Dalgleish's site to get that list to just
the unique list:
http://www.contextures.com/xladvfilter01.html#FilterUR

Plop that unique list in column B and then delete column A. (and sort the new
column A if you want).

Then a bunch of =vlookup(a2,sheet1!a:c,2,false) to fill out the rest.

========

I don't like this as much as I did before.

Nobody took a survey twice?

If that's true, then you can still merge all the worksheets into one giant
worksheet, but you're going to have to do some preliminary work.

Say sheet1 is the first survey (with 2 responses)
Put the ID in A, put the results in B:C

Sheet2 is the second survey:
ID is still in A, but the results are in D:E.

Sheet3 moves the data into F:G, but id is still A.

etc, etc, etc.

Now copy all the data into one worksheet (only keep one set of headers).

Sort by column A (all like ID's in one group).

(Save your work every now and again--just in case!)

Now run a macro that looks like this:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iCol As Long

Set wks = Worksheets("Consolidated")

With wks
FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value Then
For iCol = 2 To 12 'B to L
If .Cells(iRow, iCol).Value = "" Then
'do nothing
Else
.Cells(iRow - 1, iCol).Value = .Cells(iRow, iCol).Value
End If
Next iCol
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub

Change this line:
For iCol = 2 To 12 'B to L
to extend to all the columns you need
For iCol = 2 To 255 'B to IV????