• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to align 2 groups of columns in Excel

K

Kevy Kev

New Member
Hello all
thanks for this great forum, I hope someone can help me. I have no macro experience but would like to do the following.

I have a set of data in columns A-F and a set of data in columns H-L

I wish to align H-L with A-F using A as the ref and L as the column to guide H-L.

I want to update A-F weekly. H-L will always be the same.
When a row in H-L is not matched to A-F then I want it pushed to the bottom of H-L.

The file I want to align is available in the link below as its to big to upload and any help is very welcome.

I do have some code but it gets stuck with a Run=time error 13 Type mismatch at the bold line.
The code is below. I hope I have done it right..

Code: 
Sub AlignAF_GL()
' hiker95, 06/03/2014, ME781635
Dim o As Variant, j As Long
Dim lra As Long, lri As Long
Dim c As Range, irng As Range
Application.ScreenUpdating = False
With Sheets("Sheet1")
lra = .Cells(Rows.Count, "A").End(xlUp).Row
lri = .Cells(Rows.Count, "I").End(xlUp).Row
With .Range("I2:I" & lri)
.Value = Evaluate("IF(ISTEXT(" & .Address & "),TRIM(" & .Address & "),REPT(" & .Address & ",1))")
End With
ReDim o(1 To lri - 1, 1 To 6)
For Each c In .Range("A2:A" & lra)
Set irng = .Columns(9).Find(c, LookAt:=xlWhole)
If Not irng Is Nothing Then
j = j + 1
o(j, 1) = .Cells(irng.Row, 7)
o(j, 2) = .Cells(irng.Row, 8)
o(j, 3) = .Cells(irng.Row, 9)
o(j, 4) = .Cells(irng.Row, 10)
o(j, 5) = .Cells(irng.Row, 11)
o(j, 6) = .Cells(irng.Row, 12)
.Cells(irng.Row, 9).ClearContents
Set irng = Nothing
End If
Next c
For Each c In .Range("I2:I" & lri)
If c <> "" Then
j = j + 1
o(j, 1) = .Cells(c.Row, 7)
o(j, 2) = .Cells(c.Row, 8)
o(j, 3) = .Cells(c.Row, 9)
o(j, 4) = .Cells(c.Row, 10)
o(j, 5) = .Cells(c.Row, 11)
o(j, 6) = .Cells(c.Row, 12)
End If
Next c
.Range("G2:L" & lri).ClearContents
.Range("G2").Resize(UBound(o, 1), UBound(o, 2)) = o
End With
Application.ScreenUpdating = True
End Sub

Many thanks,
Kevin

https://app.box.com/s/oaddyoumjakr7603evi9
 
just seen that the Bold is not active. Its this line the Marco gets stuck on.
Does anyone have an idea what I can do?

Regards,
K

If c <> ""Then
 
Hi Kevin ,

When the error message pops up , click on the Debug button , and in the Immediate window , type in the following :

?c.Row

You will see the number 10380 displayed ; go to cell I10380 , and you can see that the cell contains an error value #VALUE! ; correct this , and rerun the macro.

Narayan
 
Hi Kevin ,

Sorry , but I am not getting any error with the file you uploaded ; when I run the macro , it runs to completion.

You can try debugging on your own , by clicking on the Debug button , and in the Immediate window , typing in :

?j ; o(j,1) ; c.Value ; c.Row ; .Cells(c.Row , 7)

In case you can get these details , and the problem is not resolved , please post back.

Narayan
 
Hello Narayan
I too am getting it to run now but its not doing what i would like.
below is a sample of the file how it is and also one of how i would like it.
You can see that when I run the macro its does not do that.
Really grateful for your help.
Regards,
Kevin
 

Attachments

Hi Kevin ,

I don't think I can go through the two files you have uploaded , and troubleshoot the code ; we will have to start with the initial file with the original data , execute the code , and see where it is going wrong.

Instead of the two files , if you can explain what you expect the code to do , it will make it easy.

If you have already done this in your initial posts , just say so , and I will go through them once more to get an idea of your requirement.

Either way , it will take some time , so I hope it is not urgent.

Narayan
 
Hello Narayan
Thanks again.
I will start again as I am confusing myself(sorry)

I have a set of data in columns A-F and a set of data in columns G-K

I wish to align G-K with A-F using A as the ref and I as the column to guide G-K.

I want to update A-F weekly. G-K will always be the same.
When a row in H-L is not matched to A-F then I want it pushed to the bottom of G-K.

So for example Column A Row 1 has Royal Hospital London
I wish that Columns G-K find Royal Hospital London in Column I and then align G-K into row 1
repeat for all rows in Column A.
Does that make sense?
The macro runs but it put the wrong rows aligned with the rows in A-F
Here is the file one more time.
Regards and apologies for my problem.

https://app.box.com/s/w40y7p5iv49v9xaeq76u
 
Hi Kevin ,

Everyone on this forum is willing to help ; the problem is usually that those who ask for help are unwilling to wait !

Your file has the text :

Royal London Hospital

in cell A1 ; in column I , the matches for this text are :

Royal London Hospital - Outpatients --------- Row 1901
Royal London Hospital Museum & Archives -- Row 14701
Royal London Hospital Student Hostel ------- Row 14702

Can you explain what is supposed to happen when a match is found ?

Narayan
 
Hello Narayan
Thanks for the reply. For this example there are many returns but for the majority there will be only one direct match. To make it easy I would add the words "- Outpaitients" to column A so I direct match is found. This would align with 1901 and then the remaining 2 would be pushed to the bottom while the direct would align.
Does that make sense?
I really appreciate your time so thank you.
 
Hi Kevin ,

Just a minute ; does it mean that we will always consider only the first match , in case there is more than one ?

What is the meaning of align with 1901 , and the remaining two would be pushed to the bottom ?

We have 4 rows of data : row 1 , row 1901 , row 14701 and row 14702.

What would happen to these rows ? Are you saying that only column I , say cell I1901 , would be affected ?

Please remember that what you deal with day in and day out is something totally alien to someone who is trying to answer your question ; the more time and effort you invest in explaining things , the easier you make it for the other person , who is also investing time and effort in trying to understand your problem and answer it.

Narayan
 
Column A is the master column.
All direct matches only not any variations of need to be aligned.
So I would need to modify A if there are no direct matches with I
All other non matches become pushed to the bottom of I in A-Z order.
Sorry for the confusion or any misunderstanding of the rules. I am very grateful for the help offered.
 
You must log in or register to reply here.
Back
Top