Divide data from a list to different sheets

[KimmoT]

I have a raw data table in excel in the following format (simplified example):

CATS
Siam	16561	1.54
Mixed	15656	1.54
DOGS
Bulldog	14000	1.20
Labrador	12500	1.10
BMW
Black	25000	1.98
White	25000	1.99
MB
RED	28000	2.03
BLUE	28000	2.01

This Data is on a separate data sheet. Then I have two other sheets, "ANIMALS" and "CARS". The data from the data sheet should be divided to the correct sheets, so DOGS & CATS --> ANIMALS, BMW & MB --> CARS. Just add the lists after each other. The group names (DOGS, CATS, BMW and MB) are static, but the size of the groups change.

I am somehow struggling what would be the easiest and simplest way to tackle this. Any tips would be appreciated!

Thanks in advance!

 

[Gary McMaster]

Hi KimmoT,

Welcome aboard!

I'm afraid your sample data is too simplified or at least much more sanitized than I suspect it really is.

Answers to questions similar to the following will be important in order for someone to help you.

Are Cats, Dogs, BMW, MB always on a row all by themselves?
Are Cats, Dogs, BMW, MB always bold?
Are there always two rows under each category (Cats, Dogs, BMW, MB)?
Are there always 3 populated columns in non-category rows?

How do I tell the difference between a car and an animal if I see "Impala" (as in Chevrolet)

Anything else that makes entries similar or unique to help identify which data goes where.

You can use Excel Jeanie to show some actual data from your sheet on this message board. The posting of un-sanitized data would be very helpful.

 

[JLGWhiz]

I have a raw data table in excel in the following format (simplified example):

CATS
Siam	16561	1.54
Mixed	15656	1.54
DOGS
Bulldog	14000	1.20
Labrador	12500	1.10
BMW
Black	25000	1.98
White	25000	1.99
MB
RED	28000	2.03
BLUE	28000	2.01

This Data is on a separate data sheet. Then I have two other sheets, "ANIMALS" and "CARS". The data from the data sheet should be divided to the correct sheets, so DOGS & CATS --> ANIMALS, BMW & MB --> CARS. Just add the lists after each other. The group names (DOGS, CATS, BMW and MB) are static, but the size of the groups change.

I am somehow struggling what would be the easiest and simplest way to tackle this. Any tips would be appreciated!

Thanks in advance!

Well, the computer is not smart enough to tell the difference between an animal and a car, so the programmer would have to do that by using an aray for animal types and one for cars. The arrays could then be used in the code like:

Dim ary1 As Variant, ary2 As Variant, c As Range, i As Long
ary1 = Array("Cats", "Dogs")
ary2 = Array("BMW", "BM")
For i = LBound(ary1) To UBound(ary2)
    With ActiveSheet
        For Each c In .Range("A1", .Cells(Rows.Count, 1).End(xlUp))
            If c = ary1(i) Then
                'code to manipulate other data here
            End If
        Next
    End With
Next

Do the same thing with ary2.