Difficult Comparison

[Decode]

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,

 

[Lewiy]

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.

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]

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

 

[Lewiy]

Which line does it error out on?

 

[Decode]

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]

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]

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]

Funnily enough, using bold as the determining factor was my initial thought and it should have been straight forward with:

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]

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