Sort and separate data based on certain criteria

mDgo0d4mE · Oct 11, 2017

Hello i'm trying to group the CODE values and get a total of the count based on certain instances. i cant use a pivot table when doing this so VBA code is preferred

Then i would like to do the same but for "Coconut" only in the FRUIT2 Column using certain instances
Then i would like to know the only the "Apple" only in the FRUIT1 Column using certain instances.
and so on and so on...for only the Fruit1 fields.

All of this data is on an open workbook, would like move it to another.
Here is my current Code, im just not should what i should do next for sorting.

Code:

Sub CopyColumnToWorkbook()Dim sourceColumn As Range, targetcolumn As Range
Set sourceColumn = Workbooks("test1.csv").Worksheets(1).Columns("A")
Set targetcolumn = Workbooks("test2.xlsm").Worksheets(1).Columns("A")
sourceColumn.Copy Destination:=targetcolumn
ActiveSheet.Range("A1:A10000").RemoveDuplicates Columns:=Array(1), Header:=xlYes
End Sub

Here is the Current data i have.
NOTE: Grey and "Blank" should be combined with the white value.

A	B	C	D	E	F
CODE	FRUIT1	FRUIT2	YES/NO	COLOR	COUNT
1	APPLE	COCONUT	YES	RED	1
1	ORANGE	COCONUT	YES	GREEN	1
1	BANANA	COCONUT	YES	WHITE	1
1	STRAWBERRY	COCONUT	YES	GREY	1
1	GRAPE	COCONUT	YES		1
1	CHERRY	COCONUT	YES	RED	1
1	PINEAPPLE	COCONUT	YES	GREEN	1
1	PEAR	COCONUT	YES	WHITE	1
1	CORN	COCONUT	YES	GREY	1
1	CORN	COCONUT	YES		1
1	CORN	APPLE	NO	WHITE	1
1	CORN	ORANGE	NO	WHITE	1
1	CORN	BANANA	NO	WHITE	1
1	CORN	STRAWBERRY	NO	WHITE	1
1	CORN	GRAPE	NO	WHITE	1
2	APPLE	COCONUT	YES	RED	1
2	ORANGE	COCONUT	YES	GREEN	1
2	BANANA	COCONUT	YES	WHITE	1
2	STRAWBERRY	COCONUT	YES	GREY	1
2	GRAPE	COCONUT	YES		1
2	CHERRY	COCONUT	YES	RED	1
2	PINEAPPLE	COCONUT	YES	GREEN	1
2	PEAR	COCONUT	YES	WHITE	1
2	CORN	COCONUT	YES	GREY	1
2	CORN	COCONUT	YES		1
2	CORN	APPLE	NO	WHITE	1
2	CORN	ORANGE	NO	WHITE	1
2	CORN	BANANA	NO	WHITE	1
2	CORN	STRAWBERRY	NO	WHITE	1
2	CORN	GRAPE	NO	WHITE	1

Here is the final result im kinda looking for with a total row added below with the Z column blank then starting a new group "COCONUT"

A

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

CODE

TOTAL

TOTAL-YES

TOTAL-NO

TOTAL-RED

TOTAL-GREEN

TOTAL-WHITE/GREY/BLANK

YES+RED

NO+RED

YES+GREEN

NO+GREEN

YES+WHITE/GREY/BLANK

NO+WHITE/GREY/BLANK

1

15

10

5

2

11

2

0

2

0

6

5

2

15

10

5

2

11

2

0

2

0

6

5

TOTAL

30

20

10

4

22

4

0

4

0

12

10

Any Help would be greatly appreciated, ive been trying to wrap my head around how to logically think of a way to sort this out using a macro.

Thanks

MickG · Oct 12, 2017

Re: How can i sort and separate data based on certain criteria's?

Try this for the basic concept. The results will show on sheet2 starting "A1".

Code:

[COLOR="Navy"]Sub[/COLOR] MG12Oct05
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] R [COLOR="Navy"]As[/COLOR] Range, K [COLOR="Navy"]As[/COLOR] Variant
c = 1
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn
    [COLOR="Navy"]Else[/COLOR]
         [COLOR="Navy"]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
ReDim ray(1 To .Count + 2, 1 To 13)
ray(1, 1) = "CODE": ray(1, 2) = "TOTAL": ray(1, 3) = "TOTAL-YES": ray(1, 4) = "TOTAL-NO": ray(1, 5) = "TOTAL-RED"
ray(1, 6) = "TOTAL-GREEN": ray(1, 7) = "TOTAL-WHITE/GREY/BLANK": ray(1, 8) = "YES+RED": ray(1, 9) = "NO+RED"
ray(1, 10) = "YES+GREEN": ray(1, 11) = "NO+GREEN": ray(1, 12) = "YES+WHITE/GREY/BLANK": ray(1, 13) = "NO+WHITE/GREY/BLANK"
 
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .Keys
    c = c + 1
    ray(c, 1) = K: ray(c, 2) = .Item(K).Count
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] .Item(K)
            [COLOR="Navy"]If[/COLOR] R.Offset(, 3).Value = "YES" [COLOR="Navy"]Then[/COLOR] ray(c, 3) = ray(c, 3) + 1
            [COLOR="Navy"]If[/COLOR] R.Offset(, 3).Value = "NO" [COLOR="Navy"]Then[/COLOR] ray(c, 4) = ray(c, 4) + 1
            [COLOR="Navy"]If[/COLOR] R.Offset(, 4).Value = "RED" [COLOR="Navy"]Then[/COLOR] ray(c, 5) = ray(c, 5) + 1
            [COLOR="Navy"]If[/COLOR] R.Offset(, 4).Value = "GREEN" [COLOR="Navy"]Then[/COLOR] ray(c, 6) = ray(c, 6) + 1
            [COLOR="Navy"]If[/COLOR] R.Offset(, 4).Value = "WHITE" Or R.Offset(, 4).Value = "GREY" Or R.Offset(, 4).Value = vbNullString [COLOR="Navy"]Then[/COLOR] ray(c, 7) = ray(c, 7) + 1
            [COLOR="Navy"]If[/COLOR] R.Offset(, 3).Value = "YES" And R.Offset(, 4).Value = "RED" [COLOR="Navy"]Then[/COLOR] ray(c, 8) = ray(c, 8) + 1
            [COLOR="Navy"]If[/COLOR] R.Offset(, 3).Value = "NO" And R.Offset(, 4).Value = "RED" [COLOR="Navy"]Then[/COLOR] ray(c, 9) = ray(c, 9) + 1
            [COLOR="Navy"]If[/COLOR] R.Offset(, 3).Value = "YES" And R.Offset(, 4).Value = "GREEN" [COLOR="Navy"]Then[/COLOR] ray(c, 10) = ray(c, 10) + 1
            [COLOR="Navy"]If[/COLOR] R.Offset(, 3).Value = "NO" And R.Offset(, 4).Value = "GREEN" [COLOR="Navy"]Then[/COLOR] ray(c, 11) = ray(c, 11) + 1
            [COLOR="Navy"]If[/COLOR] R.Offset(, 3).Value = "YES" And R.Offset(, 4).Value = "WHITE" Or R.Offset(, 4).Value = "GREY" Or R.Offset(, 4).Value = vbNullString [COLOR="Navy"]Then[/COLOR] ray(c, 12) = ray(c, 12) + 1
            [COLOR="Navy"]If[/COLOR] R.Offset(, 3).Value = "NO" And R.Offset(, 4).Value = "WHITE" Or R.Offset(, 4).Value = "GREY" Or R.Offset(, 4).Value = vbNullString [COLOR="Navy"]Then[/COLOR] ray(c, 13) = ray(c, 13) + 1
     [COLOR="Navy"]Next[/COLOR] R
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]Dim[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]For[/COLOR] Ac = 1 To UBound(ray, 2)
    [COLOR="Navy"]For[/COLOR] n = 2 To c
        [COLOR="Navy"]If[/COLOR] IsEmpty(ray(n, Ac)) [COLOR="Navy"]Then[/COLOR] ray(n, Ac) = 0
        ray(UBound(ray, 1), Ac) = ray(UBound(ray, 1), Ac) + ray(n, Ac)
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] Ac
ray(UBound(ray, 1), 1) = "TOTAL"
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(UBound(ray, 1), 13)
    .Value = ray
    .Borders.Weight = 2
    .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

mDgo0d4mE · Oct 12, 2017

Re: How can i sort and separate data based on certain criteria's?

Hey Mick!

Thank you so much, this is the logic i am looking for. i was able to get it to work with changing the data around as well.

My next few questions is that i currently have a template set up for all this data to be moved to with the macro inside that template.

How hard would it be if i have one workbook open that has the raw data (lets call it "data.csv") and the other my workbook with my template ("Template.xlsm")
I have more raw data that goes with the "CODE" column that ranges from B2:N1000+ that needs to stay in the same row with the specific code number.
(this is what the data.csv looks like) sorry that i didnt include it in the first post, i tried to make it simple at first and get the logic across)
A B C D E F G H I J K L M N O P Q R S
CODE DATA1 DATA2 DATA3 DATA4 DATA5 DATA6 DATA7 DATA8 DATA9 DATA10 DATA11 DATA12 DATA13 FRUIT1 FRUIT2 YES/NO COLOR COUNT
1 Z Z Z Z Z Z Z Z Z Z Z Z Z APPLE COCONUT YES RED 1

<tbody>
</tbody>

Then i guess my followup question would be for the the Template.xlsm file after adding the total that you produced, to then separate out just like the total group but with "COCONUTS" FROM FRUIT2 COLUMN ONLY
THEN AFTER THAT JUST THE "APPLES" FROM FRUIT1 COLUMN.

Again, this is great stuff! Thanks for your response

MickG · Oct 14, 2017

Re: How can i sort and separate data based on certain criteria's?

Try this for "Template.xlsm" and "Data.csv"

Code:

[COLOR="Navy"]Sub[/COLOR] MG14Oct50
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] col [COLOR="Navy"]As[/COLOR] Variant, nRw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] oSet [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] R [COLOR="Navy"]As[/COLOR] Range, K [COLOR="Navy"]As[/COLOR] Variant, Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
c = 1: nRw = 2
Rw = Workbooks("Data.csv").Sheets("Data").Cells(1).CurrentRegion.Rows.Count
ReDim Ray(1 To Rw * 3, 1 To 13)
Ray(1, 1) = "CODE": Ray(1, 2) = "TOTAL": Ray(1, 3) = "TOTAL-YES": Ray(1, 4) = "TOTAL-NO": Ray(1, 5) = "TOTAL-RED"
Ray(1, 6) = "TOTAL-GREEN": Ray(1, 7) = "TOTAL-WHITE/GREY/BLANK": Ray(1, 8) = "YES+RED": Ray(1, 9) = "NO+RED"
Ray(1, 10) = "YES+GREEN": Ray(1, 11) = "NO+GREEN": Ray(1, 12) = "YES+WHITE/GREY/BLANK": Ray(1, 13) = "NO+WHITE/GREY/BLANK"
 
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] col [COLOR="Navy"]In[/COLOR] Array(1, 15, 16)
[COLOR="Navy"]With[/COLOR] Workbooks("Data.csv").Sheets("data")
[COLOR="Navy"]Set[/COLOR] Rng = .Range(.Cells(2, col), .Cells(Rows.Count, col).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn
    [COLOR="Navy"]Else[/COLOR]
         [COLOR="Navy"]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .Keys
    c = c + 1
    Ray(c, 1) = K: Ray(c, 2) = .Item(K).Count
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] .Item(K)
             [COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] col
                [COLOR="Navy"]Case[/COLOR] 1: oSet = 16
                [COLOR="Navy"]Case[/COLOR] 15: oSet = 2
                [COLOR="Navy"]Case[/COLOR] 16: oSet = 1
            [COLOR="Navy"]End[/COLOR] Select
            
            [COLOR="Navy"]If[/COLOR] R.Offset(, oSet).Value = "YES" [COLOR="Navy"]Then[/COLOR] Ray(c, 3) = Ray(c, 3) + 1
            [COLOR="Navy"]If[/COLOR] R.Offset(, oSet).Value = "NO" [COLOR="Navy"]Then[/COLOR] Ray(c, 4) = Ray(c, 4) + 1
            [COLOR="Navy"]If[/COLOR] R.Offset(, oSet + 1).Value = "RED" [COLOR="Navy"]Then[/COLOR] Ray(c, 5) = Ray(c, 5) + 1
            [COLOR="Navy"]If[/COLOR] R.Offset(, oSet + 1).Value = "GREEN" [COLOR="Navy"]Then[/COLOR] Ray(c, 6) = Ray(c, 6) + 1
            [COLOR="Navy"]If[/COLOR] R.Offset(, oSet + 1).Value = "WHITE" Or R.Offset(, oSet + 1).Value = "GREY" Or R.Offset(, oSet + 1).Value = vbNullString [COLOR="Navy"]Then[/COLOR] Ray(c, 7) = Ray(c, 7) + 1
            [COLOR="Navy"]If[/COLOR] R.Offset(, oSet).Value = "YES" And R.Offset(, oSet + 1).Value = "RED" [COLOR="Navy"]Then[/COLOR] Ray(c, 8) = Ray(c, 8) + 1
            [COLOR="Navy"]If[/COLOR] R.Offset(, oSet).Value = "NO" And R.Offset(, oSet + 1).Value = "RED" [COLOR="Navy"]Then[/COLOR] Ray(c, 9) = Ray(c, 9) + 1
            [COLOR="Navy"]If[/COLOR] R.Offset(, oSet).Value = "YES" And R.Offset(, oSet + 1).Value = "GREEN" [COLOR="Navy"]Then[/COLOR] Ray(c, 10) = Ray(c, 10) + 1
            [COLOR="Navy"]If[/COLOR] R.Offset(, oSet).Value = "NO" And R.Offset(, oSet + 1).Value = "GREEN" [COLOR="Navy"]Then[/COLOR] Ray(c, 11) = Ray(c, 11) + 1
            [COLOR="Navy"]If[/COLOR] R.Offset(, oSet).Value = "YES" And R.Offset(, oSet + 1).Value = "WHITE" Or R.Offset(, oSet).Value = "YES" And R.Offset(, oSet + 1).Value = "GREY" Or R.Offset(, oSet).Value = "YES" And R.Offset(, oSet + 1).Value = vbNullString [COLOR="Navy"]Then[/COLOR] Ray(c, 12) = Ray(c, 12) + 1
            [COLOR="Navy"]If[/COLOR] R.Offset(, oSet).Value = "NO" And R.Offset(, oSet + 1).Value = "WHITE" Or R.Offset(, oSet).Value = "NO" And R.Offset(, oSet + 1).Value = "GREY" Or R.Offset(, oSet).Value = "NO" And R.Offset(, oSet + 1).Value = vbNullString [COLOR="Navy"]Then[/COLOR] Ray(c, 13) = Ray(c, 13) + 1
     [COLOR="Navy"]Next[/COLOR] R
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]Dim[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
c = c + 1
[COLOR="Navy"]For[/COLOR] Ac = 2 To UBound(Ray, 2)
    [COLOR="Navy"]For[/COLOR] n = nRw To c - 1
       '[COLOR="Green"][B] If IsEmpty(Ray(n, Ac)) Then Ray(n, Ac) = 0 ' <<< If you want "0" intead of blanks add this line[/B][/COLOR]
        Ray(c, Ac) = Ray(c, Ac) + Ray(n, Ac)
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] Ac
Ray(c, 1) = "TOTAL"
[COLOR="Navy"]End[/COLOR] With
c = c + 1
nRw = c
[COLOR="Navy"]Next[/COLOR] col
[COLOR="Navy"]With[/COLOR] Workbooks("Template.xlsm").Sheets("Sheet1").Range("A1").Resize(c, 13)
    .ClearContents
    .Value = Ray
    .Borders.Weight = 2
    .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

mDgo0d4mE · Oct 16, 2017

Re: How can i sort and separate data based on certain criteria's?

hey MickG,

Thank you for your reply, i was able to test and it only shows it going down column A and it copys over my row 1 text i have in my template.

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

CODE

TOTAL

TOTAL-YES

TOTAL-NO

TOTAL-RED

TOTAL-GREEN

TOTAL-WHITE/GREY/BLANK

YES+RED

NO+RED

YES+GREEN

NO+GREEN

YES+WHITE/GREY/BLANK

NO+WHITE/GREY/BLANK

CODE

DATA1

DATA2

DATA3

DATA4

DATA5

DATA6

DATA7

DATA8

DATA9

DATA10

DATA11

DATA12

same code as you posted.

Code:

[COLOR=Navy]Sub[/COLOR][COLOR=#574123] MG14Oct50[/COLOR][COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] col [COLOR=Navy]As[/COLOR] Variant, nRw [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] oSet [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Dim[/COLOR] c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] R [COLOR=Navy]As[/COLOR] Range, K [COLOR=Navy]As[/COLOR] Variant, Rw [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
c = 1: nRw = 2
Rw = Workbooks("Data.csv").Sheets("Data").Cells(1).CurrentRegion.Rows.Count
ReDim Ray(1 To Rw * 3, 1 To 13)
Ray(1, 1) = "CODE": Ray(1, 2) = "TOTAL": Ray(1, 3) = "TOTAL-YES": Ray(1, 4) = "TOTAL-NO": Ray(1, 5) = "TOTAL-RED"
Ray(1, 6) = "TOTAL-GREEN": Ray(1, 7) = "TOTAL-WHITE/GREY/BLANK": Ray(1, 8) = "YES+RED": Ray(1, 9) = "NO+RED"
Ray(1, 10) = "YES+GREEN": Ray(1, 11) = "NO+GREEN": Ray(1, 12) = "YES+WHITE/GREY/BLANK": Ray(1, 13) = "NO+WHITE/GREY/BLANK"
 
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] col [COLOR=Navy]In[/COLOR] Array(1, 15, 16)
[COLOR=Navy]With[/COLOR] Workbooks("Data.csv").Sheets("data")
[COLOR=Navy]Set[/COLOR] Rng = .Range(.Cells(2, col), .Cells(Rows.Count, col).End(xlUp))
[COLOR=Navy]End[/COLOR] With
[COLOR=Navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
    [COLOR=Navy]If[/COLOR] Not .exists(Dn.Value) [COLOR=Navy]Then[/COLOR]
        .Add Dn.Value, Dn
    [COLOR=Navy]Else[/COLOR]
         [COLOR=Navy]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR]
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] K [COLOR=Navy]In[/COLOR] .Keys
    c = c + 1
    Ray(c, 1) = K: Ray(c, 2) = .Item(K).Count
    [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] R [COLOR=Navy]In[/COLOR] .Item(K)
             [COLOR=Navy]Select[/COLOR] [COLOR=Navy]Case[/COLOR] col
                [COLOR=Navy]Case[/COLOR] 1: oSet = 16
                [COLOR=Navy]Case[/COLOR] 15: oSet = 2
                [COLOR=Navy]Case[/COLOR] 16: oSet = 1
            [COLOR=Navy]End[/COLOR] Select
            
            [COLOR=Navy]If[/COLOR] R.Offset(, oSet).Value = "YES" [COLOR=Navy]Then[/COLOR] Ray(c, 3) = Ray(c, 3) + 1
            [COLOR=Navy]If[/COLOR] R.Offset(, oSet).Value = "NO" [COLOR=Navy]Then[/COLOR] Ray(c, 4) = Ray(c, 4) + 1
            [COLOR=Navy]If[/COLOR] R.Offset(, oSet + 1).Value = "RED" [COLOR=Navy]Then[/COLOR] Ray(c, 5) = Ray(c, 5) + 1
            [COLOR=Navy]If[/COLOR] R.Offset(, oSet + 1).Value = "GREEN" [COLOR=Navy]Then[/COLOR] Ray(c, 6) = Ray(c, 6) + 1
            [COLOR=Navy]If[/COLOR] R.Offset(, oSet + 1).Value = "WHITE" Or R.Offset(, oSet + 1).Value = "GREY" Or R.Offset(, oSet + 1).Value = vbNullString [COLOR=Navy]Then[/COLOR] Ray(c, 7) = Ray(c, 7) + 1
            [COLOR=Navy]If[/COLOR] R.Offset(, oSet).Value = "YES" And R.Offset(, oSet + 1).Value = "RED" [COLOR=Navy]Then[/COLOR] Ray(c, 8) = Ray(c, 8) + 1
            [COLOR=Navy]If[/COLOR] R.Offset(, oSet).Value = "NO" And R.Offset(, oSet + 1).Value = "RED" [COLOR=Navy]Then[/COLOR] Ray(c, 9) = Ray(c, 9) + 1
            [COLOR=Navy]If[/COLOR] R.Offset(, oSet).Value = "YES" And R.Offset(, oSet + 1).Value = "GREEN" [COLOR=Navy]Then[/COLOR] Ray(c, 10) = Ray(c, 10) + 1
            [COLOR=Navy]If[/COLOR] R.Offset(, oSet).Value = "NO" And R.Offset(, oSet + 1).Value = "GREEN" [COLOR=Navy]Then[/COLOR] Ray(c, 11) = Ray(c, 11) + 1
            [COLOR=Navy]If[/COLOR] R.Offset(, oSet).Value = "YES" And R.Offset(, oSet + 1).Value = "WHITE" Or R.Offset(, oSet).Value = "YES" And R.Offset(, oSet + 1).Value = "GREY" Or R.Offset(, oSet).Value = "YES" And R.Offset(, oSet + 1).Value = vbNullString [COLOR=Navy]Then[/COLOR] Ray(c, 12) = Ray(c, 12) + 1
            [COLOR=Navy]If[/COLOR] R.Offset(, oSet).Value = "NO" And R.Offset(, oSet + 1).Value = "WHITE" Or R.Offset(, oSet).Value = "NO" And R.Offset(, oSet + 1).Value = "GREY" Or R.Offset(, oSet).Value = "NO" And R.Offset(, oSet + 1).Value = vbNullString [COLOR=Navy]Then[/COLOR] Ray(c, 13) = Ray(c, 13) + 1
     [COLOR=Navy]Next[/COLOR] R
[COLOR=Navy]Next[/COLOR] K
[COLOR=Navy]Dim[/COLOR] Ac [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
c = c + 1
[COLOR=Navy]For[/COLOR] Ac = 2 To UBound(Ray, 2)
    [COLOR=Navy]For[/COLOR] n = nRw To c - 1
       '[COLOR=Green][B] If IsEmpty(Ray(n, Ac)) Then Ray(n, Ac) = 0 ' <<< If you want "0" intead of blanks add this line[/B][/COLOR]
        Ray(c, Ac) = Ray(c, Ac) + Ray(n, Ac)
    [COLOR=Navy]Next[/COLOR] n
[COLOR=Navy]Next[/COLOR] Ac
Ray(c, 1) = "TOTAL"
[COLOR=Navy]End[/COLOR] With
c = c + 1
nRw = c
[COLOR=Navy]Next[/COLOR] col
[COLOR=Navy]With[/COLOR] Workbooks("Template.xlsm").Sheets("Sheet1").Range("A1").Resize(c, 13)
    .ClearContents
    .Value = Ray
    .Borders.Weight = 2
    .Columns.AutoFit
[COLOR=Navy]End[/COLOR] [COLOR=Navy]With[/COLOR] [COLOR=Navy]End[/COLOR][COLOR=#574123] [/COLOR][COLOR=Navy]Sub[/COLOR]

MickG · Oct 16, 2017

Re: How can i sort and separate data based on certain criteria's?

My code takes the Data (cvs file) in column "A" along with columns "O to S" runs the code on them and places the results in "Template" file.
Do you require for the column "B to N" in .cvs file to also be transferred to "Template File", with the other results, because I'm not sure exactly how the results should look, could you show a couple of complete rows from the ".csv" file and also the "Template" file.

mDgo0d4mE · Oct 16, 2017

Re: How can i sort and separate data based on certain criteria's?

Hey MickG,

Yes i request columns B through M ( i think i made the mistake when building it on the forum) in .cvs file to be also transferred as well to "Template File"
all the data matches that CODE so it just needs to be in the same row. as code "1" then code "2" ect...ect.... also the codes doesn't follow in and numeric order.

Total Group > Coconuts only group > Apples Only > Oranges Only.......(template.xlsm)
Coconuts from only Fruit2 Column (O) "data.csv"
then only the fruit from Fruit1 Column (N) "data.csv"

i went ahead and attached some links to the google drive where they are located at. if i can upload them somewhere else to easily view and download them i can.

https://drive.google.com/file/d/0B-kSC2Lpyp_7ZGU1TlRnY05PY1pHd3hwVFpWX1dKcUZSSmVj/view?usp=sharing
is the data.csv file
https://drive.google.com/file/d/0B-kSC2Lpyp_7QWdkVEc3ZUVHeXJVbng1LUEwSmlGdU1fa2Fv/view?usp=sharing
is the Template.xlsm

here is the completed file i request to look like if all possible.
https://drive.google.com/file/d/0B-kSC2Lpyp_7R2Z6WnpaT19wZFBpWDg1RTN2MndKWEh6WWNN/view?usp=sharing

MickG your doing magic here and im also learning a ton while you going through this with me.
Thank you so much!!

Sort and separate data based on certain criteria

mDgo0d4mE

New Member

Excel Facts

MickG

MrExcel MVP

mDgo0d4mE

New Member

MickG

MrExcel MVP

mDgo0d4mE

New Member

MickG

MrExcel MVP

mDgo0d4mE

New Member

Similar threads

Forum statistics

Share this page

Sort and separate data based on certain criteria

mDgo0d4mE

New Member

Excel Facts

MickG

MrExcel MVP

mDgo0d4mE

New Member

MickG

MrExcel MVP

mDgo0d4mE

New Member

MickG

MrExcel MVP

mDgo0d4mE

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock