Solved: Moving data and deleting duplicate rows

[tpoynton]

Having a hard time wrapping my head around this and could use some help! I've posted a sanitized workbook for reference.

The workbook contains data about the various types of employees who work in a bunch of different buildings. Right now, the data are organized by job type, and I need them organized by building so that there is only 1 row per building code (BLDG_CODE in the attached). I need to do this because I need to merge this with another dataset that is keyed by building. There are over 3000 rows, hence my desire to automate, even though this will only be used a handful of times.

Instead of going into too much detail here, I've manually done what I would like a macro to do in the attached workbook. black text are what the original data look like, and green is what I want it to look like in the end - except that I want all the rows below the first instance of the BLDG_CODE deleted.

SO, in each BLDG (column A) there are potentially 8 different JOBS (column B). Each building does not have every job, and some buildings only have 1 listed job. 'For each' BLDG_CODE, I need to copy and paste the info in columns C, D, E, and F into the appropriately labeled 4 columns (based on Job#). If there is more than one Job in a BLDG, I need to have all of the JOB info in a single row, then delete the rows below.

Hopefully this is transparent in the workbook.

I dont need someone to write the whole thing for me; I just need some help getting started. I'm having trouble conceptualizing how to move the data below the first instance of a unique BLDG_CODE to the row containing the first instance, given that the number of different jobs is not consistent...Using Offset seems like a good technique, but figuring out how many duplicate BLDG_CODE's there are to feed into the Offset is eluding me...

Thanks for your consideration! tim

[Bob Phillips]

Tim,

Hopefully this will get you started.

First off, you gotta work bottom up, otherwise you soon get royally messed. As the data looks contiguous, determing the last row is a straightforward

I wouldn't bother trying to preserve or deal with the real data columns in the loop, leave them to last.

You need to loop lastrow to 3rd (not first, that is headings, not 2nd as you compare against previous), and see if the current row value column A is the same as the previous row value column A.
- for either case, same or not, you need to get the job number, that is a simple Right function for 1 character
- for either case you need to deduce the first column to drop the job details - this is easily obtained as the fist is column 7, second 11 and so on, so if the job number is iJob, it is iJob *4 + 3
- if the same, copy cells C:F (I use Resize fo this type of thing - Cells(i,"C").Resize(1,4).Copy) to the correct job cell as deduced above, but to the PREVIOUS row, and delete the current row
- if not the same opy cells C:F to the correct job cell as deduced above, but to the SAME row

pseudo-codish


.....loop number of jobs
........setup each column heading for this job
.....end loop

.....loop all rows bar 1& 2 from last row, backwards
.........setup current row job number into variable
.........If current row building num = previous row building number
............copy columns C:F of current row to job slot in previous row
............delete current row
.........Else
............copy columns C:F of current row to job slot in same row
......end.loop
......delete.columns B:F

[tpoynton]

Thanks Bob - I'll work on it today and see if I can approximate your approach. I've never counted backwards before (well, there was that one time on the highway with that officer...), but a quick search reveals that step -1 will do that for me.

Looking more closely, I was initially (not surprisingly) confused, but I get it now. I had it in my head I had to do all jobs at once, but running the loop for each job and deleting solves what was the most confusing part for me. This is great!

What surprises me is that you didnt have a formula solution :), but you cant delete a row using a formula (right?).

tim

[Bob Phillips]

:rotlaugh:Correct.

Believe it or not, I am a VBer at heart, I only really took to Excel (from Lotus 1-2-3) when they introduced VBA.

[tpoynton]

Well, you really know your formulas (formulae)!

I'm working on this now, and my 'sanitization' has somewhat confused things; for example, the jobs are not called job1, job2, etc, unfortunately - they're simple text values, no numbers. But this is a relatively easy fix; I'll just change the values for where the data should be copied to for each job, and run the loop each time. Not very elegant, but since this is only going to be used a handful of times, it'll work (I hope).

I wanted to see how going backwards in the loop worked, so I ran:

for i = 3204 to 3 step - 1
msgbox i
next i

I thought I could simply break or reset the routine through the VBE, but I had to stick a pencap in the return key and wait for it to end...the msgbox took over - DOH!

[Bob Phillips]

Tim,

I'm working on this now, and my 'sanitization' has somewhat confused things; for example, the jobs are not called job1, job2, etc, unfortunately - they're simple text values, no numbers. But this is a relatively easy fix; I'll just change the values for where the data should be copied to for each job, and run the loop each time. Not very elegant, but since this is only going to be used a handful of times, it'll work (I hope).
If they are not as convenient as Job 1, Job 2, etc., you could store all possible values in an array, and Application.Match the current job number against that array to get a job index, and use the technique I alluded to. Quite neat.


I wanted to see how going backwards in the loop worked, so I ran:

for i = 3204 to 3 step - 1
msgbox i
next i
I thought I could simply break or reset the routine through the VBE, but I had to stick a pencap in the return key and wait for it to end...the msgbox took over - DOH!
Well more fool you !:whistle:

A loop will normally break ok, as long as cancel key isn't disabled, but that one is so tight, you never get a chance. As soon as you respond to one MsgBox, the next is up before you can hit Esc.

[lucas]

I thought I could simply break or reset the routine through the VBE, but I had to stick a pencap in the return key and wait for it to end...the msgbox took over - DOH!
I thought I was the only one who did things like this...I probably wouldn't have thought to auger the return key though......that's funny(now)Tim.

[tpoynton]

Thanks Bob - using application.match is neat! Since I came up with a solution that is more my speed before reading your reply, my technique isnt as efficient, but it worked! It was quite slow, as you might imagine...

If anyone is interested for some reason, I'll post the code. This doesnt exactly match the workbook I posted because it is based on the real datafile I have, but you should get the gist. The job titles are not as written below, but I have been asked to keep the information private...it's really pretty innocuous, but I do what I'm told most of the time.


Private Sub movedata()
Application.ScreenUpdating = False

Dim sJob As String
Dim iJobCol As Integer
Dim i As Integer

For i = 3204 To 3 Step -1 '3204 rows of data
sJob = Cells(i, 10)
If sJob = "Job One" Then
iJobCol = 15
ElseIf sJob = "Job two" Then
iJobCol = 19
ElseIf sJob = "Job three" Then
iJobCol = 23
'etcetera
End If

Range(Cells(i, 11), Cells(i, 14)).Copy

If Cells(i, 4).Value = Cells(i - 1, 4).Value Then
Cells(i - 1, iJobCol).PasteSpecial (xlPasteValues)
ActiveSheet.Rows(i).EntireRow.Delete
Else
Cells(i, iJobCol).PasteSpecial (xlPasteValues)
End If
Application.CutCopyMode = False
Next i

Application.ScreenUpdating = True

End Sub


Thank you very much Bob - I appreciate you making this a learning experience for me! tim

[tpoynton]

I thought I was the only one who did things like this...I probably wouldn't have thought to auger the return key though......that's funny(now)Tim.

Actually, it was quite funny then too!

[lucas]

Sometimes we do things we just have to laugh about:rotlaugh: Thanks for sharing one with us.