Find Group VBA require

[Kishan]

Using Excel 2000

Hi,

I have 4 groups
C1:C2 = Group A = 7 &7
D1:D2 = Group B = 7 &Z
E1:E2 = Group C = Z &7
F1:F2 = Group D = Z &Z

C7:F9 I have got data, I want results in H6:K9

Example how it works... with row 5 & 6
Cells C5, C6 = 7 & 7 result in H6 = A (because it belong to group A)
Cells D5, D6 = 7 & Z result in I6 = B (because it belong to group B)
Cells E5, E6 = Z & 7 result in J6 = C (because it belong to group C)
Cells F5, F6 = Z & Z result in K6 = D (because it belong to group D)

Example how it works... with row 6 & 7
Cells C6, C7 = 7 & Z result in H7 = B (because it belong to group B)
Cells D6, D7 = Z & 7 result in I7 = C (because it belong to group C)
Cells E6, E7 = 7 & Z result in J7 = B (because it belong to group B)
Cells F6, F7 = Z & Z result in K7 = D (because it belong to group D)

And will continue as same procedure with rest row till to end

Sheet example

Book1
	A	B	C	D	E	F	G	H	I	J	K
1			7	7	Z	Z
2			7	Z	7	Z
3		Group-->	A	B	C	D
4								n1	n2	n3	n4
5			7	7	Z	Z
6			7	Z	7	Z		A	B	C	D
7			Z	7	Z	Z		B	C	B	D
8			Z	7	7	7		D	A	C	C
9			7	Z	Z	Z		C	B	B	B
10
11
Sheet1

Thank you in advance

Regards,
Kishan

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG19Nov45
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("C6"), Range("C" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]For[/COLOR] Ac = 0 To 3
       [COLOR="Navy"]For[/COLOR] n = 0 To 3
            [COLOR="Navy"]If[/COLOR] Dn.Offset(-1).Offset(, Ac) & Dn.Offset(, Ac).Value _
                 = Cells(1, 3).Offset(, n) & Cells(2, 3).Offset(, n) [COLOR="Navy"]Then[/COLOR]
                Dn.Offset(, 5 + Ac).Value = Cells(3, 3).Offset(, n).Value
                [COLOR="Navy"]Exit[/COLOR] For
            [COLOR="Navy"]End[/COLOR] If
      [COLOR="Navy"]Next[/COLOR] n
    [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Kishan]

Try this:-

[COLOR=navy]Sub[/COLOR] MG19Nov45
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, Ac [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range(Range("C6"), Range("C" & Rows.Count).End(xlUp))
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    [COLOR=navy]For[/COLOR] Ac = 0 To 3
       [COLOR=navy]For[/COLOR] n = 0 To 3
            [COLOR=navy]If[/COLOR] Dn.Offset(-1).Offset(, Ac) & Dn.Offset(, Ac).Value _
                 = Cells(1, 3).Offset(, n) & Cells(2, 3).Offset(, n) [COLOR=navy]Then[/COLOR]
                Dn.Offset(, 5 + Ac).Value = Cells(3, 3).Offset(, n).Value
                [COLOR=navy]Exit[/COLOR] For
            [COLOR=navy]End[/COLOR] If
      [COLOR=navy]Next[/COLOR] n
    [COLOR=navy]Next[/COLOR] Ac
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

Thank a lot Mick, cannot believe, but I have to say it is giving a desire results as I wanted.

Good Luck and have a nice Sunday

Regards,
Kishan

 

[MickG]

You're welcome