Difficult Comparison

Decode

New Member
Joined
Apr 4, 2005
Messages
28
Hi,

I've two sets of data I need to compare - which I could work out how to do if they weren't formatted so strangely!

The data concerns product specifications and is copied from another program so is pasted into Excel and appears all in one column, eg:

FORD

Fiesta

Blue
Red
Yellow

FIAT

Uno

Green
Orange
Blue

Punto

Red
Yellow
Green

So, the Manufacturer is in Bold, the Model is a Hyperlink and the Values are in standard Ariel 10pt. Both lists arrive like this, all info in column A, on different sheets. But one list might have more Models on than the other (Hyperlinks) and values listed under that model.

It's the additional models I want to identify, and preferably what values are listed under them. (if the model is missing, the values are too).

I've tried various methods to reformat the data via macro so I can run a simple comparison but its proving really tricky. I think the best way would be to compare the hyperlinks, but I can't find a good way to a) extract and compare them on their own and b) then retrieve the values that go with them.

I can usually work out VBA problems with a few hints or suggestions so any help would be much appreciated.

Many thanks,
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
Does this help at all?

Open a new workbook, copy your list into Sheet1 and run this macro. It goes a little way into sorting the data out in Sheet2 for you. Maybe will give you some ideas for exactly what you want.
Code:
Sub CarSort()
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim Limit As Long
Dim c As Long
Dim d As Long
Dim iRow As Long
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
Limit = sh1.Cells(Rows.Count, 1).End(xlUp).Row
d = 1
iRow = 2
For c = 1 To Limit
    If sh1.Cells(c, 1) = UCase(sh1.Cells(c, 1)) And sh1.Cells(c, 1) <> "" Then
        sh2.Cells(1, d) = sh1.Cells(c, 1)
        d = d + 1
        iRow = 2
        Else
            sh2.Cells(iRow, d - 1) = sh1.Cells(c, 1)
            iRow = iRow + 1
    End If
Next c
End Sub
 

Decode

New Member
Joined
Apr 4, 2005
Messages
28
Thanks for that, much appreciated. I can see what is supposed to happen and what each piece of code is supposed to do and I know it will be useful, but it's giving a Run time Error 1004 at the moment - 'Application Defined or Object-defined error.

I'll keep playing about with it, but if anything occurs just looking at the code, please let me know.

Thanks
 

Decode

New Member
Joined
Apr 4, 2005
Messages
28
Hi,

If I step through using F8, it appears to error at

sh2.Cells(iRow, d - 1) = sh1.Cells(c, 1)
iRow = iRow + 1

best
 

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
My initial instinct would be that the value of “d” will be 1 when it errors and thus creating an invalid cell reference. The code is assuming that the car manufacturers are always displayed in capital letters and it will rely on the first manufacturers name appearing in cell A1 (i.e. it doesn’t come across any cells in column A, blank or otherwise, before it finds the first manufacturer name).
 

Decode

New Member
Joined
Apr 4, 2005
Messages
28
Thanks Lewiy,

Working now. It's really clever - am most impressed! Thank you very much. the only difficulty I'm having is that some of the Model names are in capitals too.

Would it be possible to make the condition of the If Statement that the Brands are in bold, rather than in caps?

If not no worries, I'll keep fiddling. Thanks for your help.

best
 

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
Funnily enough, using bold as the determining factor was my initial thought and it should have been straight forward with:
Code:
If sh1.Cells(c, 1).Font.Bold = True And sh1.Cells(c, 1) <> “”
However, when I tried this, it wasn’t recognising the test data as being bold (I copied and pasted direct from your post). It might be worth changing this line just to try it on your data in case it does have true bold properties. Otherwise, I’m stuck for thoughts!
 

Decode

New Member
Joined
Apr 4, 2005
Messages
28
Cheers Lewiy

On the real data the Brands do have bold properties and the Models Underlined properties, so the condition of Bold = True works perfectly.

Thanks once again. I'll need to move it around a bit more separate the brand and models in the same way, but I'll definitely be able to reach a solution with this.

best
 

Forum statistics

Threads
1,181,658
Messages
5,931,271
Members
436,785
Latest member
KingGideon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top