Find letters of the group

[motilulla]

Hello,

I have 3 groups each group has 3 letters as shown in the cells C1:K2

I have data of the group in the cells C6:F16

What I need for example...
C6 has group value 2 so extract group 2 letters D, E, F and put them in the cells H6, I6 and J6
D6 has group value 1 so extract group 1 letters A, B, C and put them in the cells L6, M6 and N6

Continue finding all the group letters and put them as shown in attached example image for a detail. Pease suggest formula or VBA

*

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

1

Group

1

1

1

2

2

2

3

3

3

2

Letter

A

B

C

D

E

F

G

H

I

3

4

Result

Result

Result

Result

Result

Result

Result

Result

Result

Result

Result

Result

5

n1

n2

n3

n4

Group

Group

Group

Group

Group

Group

Group

Group

Group

Group

Group

Group

6

2

1

3

3

D

E

F

A

B

C

G

H

I

G

H

I

7

2

3

2

1

D

E

F

G

H

I

D

E

F

A

B

C

8

2

1

3

3

D

E

F

A

B

C

G

H

I

G

H

I

9

3

1

3

2

G

H

I

A

B

C

G

H

I

D

E

F

10

1

1

1

3

A

B

C

A

B

C

A

B

C

G

H

I

11

2

2

3

1

D

E

F

D

E

F

G

H

I

A

B

C

12

2

3

1

1

D

E

F

G

H

I

A

B

C

A

B

C

13

1

2

1

3

A

B

C

D

E

F

A

B

C

G

H

I

14

3

1

2

2

G

H

I

A

B

C

D

E

F

D

E

F

15

1

1

1

1

A

B

C

A

B

C

A

B

C

A

B

C

16

1

2

3

2

A

B

C

D

E

F

G

H

I

D

E

F

17

18

19

20

21

22

Thank you all.

I am using Excel 2000

Regards,
Moti

 

[bebo021999]

Try in H6:

=IF(MOD(COLUMN(A:A)-1,4)+1=4,"",INDEX($2:$2,MATCH(INDEX($C6:$F6,INT((COLUMN(A:A)-1)/4)+1),$1:$1,0)-1+MOD(COLUMN(A:A)-1,4)+1))

Fill down and accross

 

[Zot]

Try this

Sub FillGroup()

Dim nCol As Long
Dim Grp1(), Grp2(), Grp3(), Arry()
Dim cell As Range, rngN As Range
Dim ws As Worksheet

Application.ScreenUpdating = False

Set ws = ActiveWorkbook.Sheets("Sheet1")
Set rngN = ws.Range("C6", "F16")

Grp1 = Array("A", "B", "C")
Grp2 = Array("D", "E", "F")
Grp3 = Array("G", "H", "I")

For Each cell In rngN
    Select Case cell
        Case 1
            Arry = Grp1
        Case 2
            Arry = Grp2
        Case 3
            Arry = Grp3
    End Select
    Select Case cell.Column
        Case 3
            nCol = 5
        Case 4
            nCol = 8
        Case 5
            nCol = 11
        Case 6
            nCol = 14
    End Select
        ws.Range(Cells(cell.Row, cell.Column), Cells(cell.Row, cell.Column + 2)).Offset(0, nCol) = Arry
Next
Application.ScreenUpdating = True

End Sub

 

[motilulla]

Try in H6:

=IF(MOD(COLUMN(A:A)-1,4)+1=4,"",INDEX($2:$2,MATCH(INDEX($C6:$F6,INT((COLUMN(A:A)-1)/4)+1),$1:$1,0)-1+MOD(COLUMN(A:A)-1,4)+1))

Fill down and accross

bebo021999, yes formula worked perfect.

Thank you so much for you help

Good Luck!

Kind Regards,
Moti

 

[motilulla]

Try this

Sub FillGroup()

Dim nCol As Long
Dim Grp1(), Grp2(), Grp3(), Arry()
Dim cell As Range, rngN As Range
Dim ws As Worksheet

Application.ScreenUpdating = False

Set ws = ActiveWorkbook.Sheets("Sheet1")
Set rngN = ws.Range("C6", "F16")

Grp1 = Array("A", "B", "C")
Grp2 = Array("D", "E", "F")
Grp3 = Array("G", "H", "I")

For Each cell In rngN
    Select Case cell
        Case 1
            Arry = Grp1
        Case 2
            Arry = Grp2
        Case 3
            Arry = Grp3
    End Select
    Select Case cell.Column
        Case 3
            nCol = 5
        Case 4
            nCol = 8
        Case 5
            nCol = 11
        Case 6
            nCol = 14
    End Select
        ws.Range(Cells(cell.Row, cell.Column), Cells(cell.Row, cell.Column + 2)).Offset(0, nCol) = Arry
Next
Application.ScreenUpdating = True

End Sub

Zot, I like the macro it worked fine and find it is practical an easy to apply and use of it.

Thank you so much for you help

Good Luck!

Kind Regards,
Moti

 

[Zot]

Zot, I like the macro it worked fine and find it is practical an easy to apply and use of it.

Thank you so much for you help

Good Luck!

Kind Regards,
Moti

Was trying to make it easy to understand and modified to need. Glad it is clear. For me to put formula in every rows is making file big. Maybe that's why I'm no formula expert ?