need a formula..

VBABEGINER · Apr 12, 2018

Hello All,

I need a formula...

I have data like below..

Code:

[TABLE="width: 700"]
<colgroup><col span="2"><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Department[/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[TD]Q3[/TD]
[TD]Q4[/TD]
[TD]Q5[/TD]
[TD]Q6[/TD]
[/TR]
[TR]
[TD]Howard[/TD]
[TD]IT[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Gill[/TD]
[TD]HR[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Rajesh[/TD]
[TD]REWS[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Jones[/TD]
[TD]MI[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Sorvino[/TD]
[TD]IT[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Ravi[/TD]
[TD]HR[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Sumit[/TD]
[TD]REWS[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Jardine[/TD]
[TD]MI[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Smith[/TD]
[TD]IT[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Suraj[/TD]
[TD]HR[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Morgan[/TD]
[TD]REWS[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Sameer[/TD]
[TD]MI[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Andrews[/TD]
[TD]IT[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Roshan[/TD]
[TD]HR[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Thompson[/TD]
[TD]REWS[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Mukesh[/TD]
[TD]MI[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Rajan[/TD]
[TD]IT[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Nilesh[/TD]
[TD]HR[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Kivell[/TD]
[TD]REWS[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Mohit[/TD]
[TD]MI[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Manoj[/TD]
[TD]IT[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Mihir[/TD]
[TD]HR[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

I want output in this format...

Code:

[TABLE="width: 508"]
<colgroup><col span="2"><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Ratings[/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[TD]Q3[/TD]
[TD]Q4[/TD]
[TD]Q5[/TD]
[TD]Q6[/TD]
[TD]Department Names[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: center"]#VALUE![/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

Im using this formuala..but didnt get..

Code:

(C2:C34,A38,C1:H1,B37)

Gerald Higgins · Apr 12, 2018

Hi - your input data looks hugely different from your output, and it's not clear to me how you get from one to the other.

Where should the names go ?
Where should the departments go ?

What would be really helpful is if you posted the ACTUAL RESULTS you expect to see, not just a blank space where you think the results should go.

Also, your formula seems to be nonsense - did some of it not get posted ?
Might be helpful if you post the full formula.

Arkays · Apr 12, 2018

Hi..

i hope i understand your question right, i got you a solution as per below, however you need to know that this ill return one value only since you marked Rating 1 one time only while in your original data base you have two department with rating one " MI & HR"

in the error cell " #VALUE ! " you can past the below considering Sheet 1 name is "Data Base" and sheet 2 name is " Results "

=INDEX('Data Base'!$B$2:$B$23,MATCH(Results!A2,'Data Base'!$C$2:$C$23,0))

drag this equation till Rating Since and to Q6

i hope this can help you

VBABEGINER · Apr 12, 2018

Hi,
Thanks for your reply.

I can redesign the output like this...

I am trying to see...for Question 1 how many people are giving 1 or 2 or 2 rating...

like to see the ratings across to each question...

and I can re-design it like this..so by selecting department names list...count gets change..

Code:

[B][/B][TABLE="width: 444"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Department Names[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD](List)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ratings[/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[TD]Q3[/TD]
[TD]Q4[/TD]
[TD]Q5[/TD]
[TD]Q6[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: center"]#VALUE![/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

Gerald Higgins said:
Hi - your input data looks hugely different from your output, and it's not clear to me how you get from one to the other.

Where should the names go ?
Where should the departments go ?

What would be really helpful is if you posted the ACTUAL RESULTS you expect to see, not just a blank space where you think the results should go.

Also, your formula seems to be nonsense - did some of it not get posted ?
Might be helpful if you post the full formula.

VBABEGINER · Apr 12, 2018

Hi,
Thanks for your reply.

I dont understand why you have taken "Results!A2,"..?

I will show again the structure..

Code:

[TABLE="width: 444"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Department Names[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD](List)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ratings[/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[TD]Q3[/TD]
[TD]Q4[/TD]
[TD]Q5[/TD]
[TD]Q6[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: center"]#N/A[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

Department Names = A36 Cell

if I modify your formula like below

Code:

=INDEX('Data Base'!$B$2:$B$34,MATCH(A39,'Data Base'!$C$2:$C$34,0))

Code:

[TABLE="width: 444"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Department Names[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD](List)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ratings[/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[TD]Q3[/TD]
[TD]Q4[/TD]
[TD]Q5[/TD]
[TD]Q6[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]HR[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

Arkays said:
Hi..

i hope i understand your question right, i got you a solution as per below, however you need to know that this ill return one value only since you marked Rating 1 one time only while in your original data base you have two department with rating one " MI & HR"

in the error cell " #VALUE ! " you can past the below considering Sheet 1 name is "Data Base" and sheet 2 name is " Results "

=INDEX('Data Base'!$B$2:$B$23,MATCH(Results!A2,'Data Base'!$C$2:$C$23,0))

drag this equation till Rating Since and to Q6

i hope this can help you

wideboydixon · Apr 12, 2018

This?

Book1

A

B

C

D

E

F

G

H

1

Name

Department

Q1

Q2

Q3

Q4

Q5

Q6

2

Howard

IT

3

Gill

HR

4

Rajesh

REWS

4

5

Jones

MI

4

6

Sorvino

IT

5

7

Ravi

HR

1

8

Sumit

REWS

3

9

Jardine

MI

1

10

Smith

IT

2

11

Suraj

HR

3

12

Morgan

REWS

2

13

Sameer

MI

2

14

Andrews

IT

2

15

Roshan

HR

1

16

Thompson

REWS

4

17

Mukesh

MI

3

18

Rajan

IT

3

19

Nilesh

HR

3

20

Kivell

REWS

2

21

Mohit

MI

1

22

Manoj

IT

3

23

Mihir

HR

2

37

Ratings

Q1

Q2

Q3

Q4

Q5

Q6

38

1

4

39

2

6

40

3

7

41

4

42

5

1

43

6

0

Sheet1

Cell Formulas
Range	Formula
B38	=COUNTIF(C$2:C$23,$A38)

WBD

Gerald Higgins · Apr 12, 2018

Take a look at Arkays' solution in post #3 , maybe they have guessed what you want.

If not, I would just repeat everything I said in post #2 .

In your post #4 , you seem to have just repeated much of what you said in post #1 .

What would be more helpful (to me at least) would be to address the questions in my post #2 .

markmzz · Apr 12, 2018

Hi!

Maybe the suggestion below can helps (for your first layout):

In B26 and copy down and to the right

=COUNTIF(INDEX($C$2:$H$23,,MATCH(B$25,$C$1:$H$1,0)),$A26)

In J26 and copy down and to the right

=IFERROR(INDEX($B$2:$B$23,MATCH(0,INDEX(COUNTIF($I26:I26,$B$2:$B$23)+(MMULT(--($C$2:$H$23=$A26),{1;1;1;1;1;1})=0),),0)),"")

In H26 and copy down

=SUBSTITUTE(TRIM(J26&" "&K26&" "&L26&" "&M26)," ",", ")

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Name	Department	Q1	Q2	Q3	Q4	Q5	Q6
2	Howard	IT	3	3	3	3	3	3
3	Gill	HR	4	4	4	4	4	4
4	Rajesh	REWS	4	4	4	4	4	4
5	Jones	MI	4	4	4	4	4	4
6	Sorvino	IT	5	5	5	5	5	5
7	Ravi	HR	1	1	1	1	1	1
8	Sumit	REWS	3	3	3	3	3	3
9	Jardine	MI	1	1	1	1	1	1
10	Smith	IT	2	2	2	2	2	2
11	Suraj	HR	3	3	3	3	3	3
12	Morgan	REWS	2	2	2	2	2	2
13	Sameer	MI	2	2	2	2	2	2
14	Andrews	IT	2	2	2	2	2	2
15	Roshan	HR	1	1	1	1	1	1
16	Thompson	REWS	4	4	4	4	4	4
17	Mukesh	MI	3	3	3	3	3	3
18	Rajan	IT	3	3	3	3	3	3
19	Nilesh	HR	3	3	3	3	3	3
20	Kivell	REWS	2	2	2	2	2	2
21	Mohit	MI	1	1	1	1	1	1
22	Manoj	IT	3	3	3	3	3	3
23	Mihir	HR	2	2	2	2	2	2
24
25	Ratings	Q1	Q2	Q3	Q4	Q5	Q6	Department Names		HelpCol01	HelpCol02	HelpCol03	HelpCol04
26	1	4	4	4	4	4	4	HR, MI		HR	MI
27	2	6	6	6	6	6	6	IT, REWS, MI, HR		IT	REWS	MI	HR
28	3	7	7	7	7	7	7	IT, REWS, HR, MI		IT	REWS	HR	MI
29	4	4	4	4	4	4	4	HR, REWS, MI		HR	REWS	MI
30	5	1	1	1	1	1	1	IT		IT
31	6	0	0	0	0	0	0
32
***	**********	***********	****	****	****	****	****	******************	**	**********	**********	**********	**********	**

Markmzz

VBABEGINER · Apr 12, 2018

Thanks for this...

I have re-designed this..

Code:

=COUNTIFS(C$2:C$34,$A39,$B$2:$B$34,$A$37)

and the format is,

Code:

[TABLE="width: 444"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Department Names[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ratings[/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[TD]Q3[/TD]
[TD]Q4[/TD]
[TD]Q5[/TD]
[TD]Q6[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]

wideboydixon said:
This?

VBABEGINER · Apr 12, 2018

HI,
thanks for reply..

This is something Super..

Im posting now my original records, which now i have in my hand..Can you make please graph..to see the flow..in which question, users are given high rating and low ratings..like this...

Code:

[TABLE="width: 503"]
<colgroup><col><col><col span="7"></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Business Unit[/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[TD]Q3[/TD]
[TD]Q4[/TD]
[TD]Q5[/TD]
[TD]Q6[/TD]
[TD]Q7[/TD]
[/TR]
[TR]
[TD]User 1[/TD]
[TD]Reins[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]User 2[/TD]
[TD]FINA[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]User 3[/TD]
[TD]Comp[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]User 4[/TD]
[TD]TA[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]User 5[/TD]
[TD]GC[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]User 6[/TD]
[TD]Re[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]User 7[/TD]
[TD]GB[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]User 8[/TD]
[TD]WGB[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]User 9[/TD]
[TD]GC[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]User 10[/TD]
[TD]GC[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]User 11[/TD]
[TD]GB[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]User 12[/TD]
[TD]Support[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]User 13[/TD]
[TD]GC[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]User 14[/TD]
[TD]GB[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]User 15[/TD]
[TD]GC[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]User 31[/TD]
[TD]NA[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]User 32[/TD]
[TD]NA[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]User 33[/TD]
[TD]NA[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]User 34[/TD]
[TD]NA[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]User 35[/TD]
[TD]NA[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]User 36[/TD]
[TD]NA[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]User 37[/TD]
[TD]NA[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]User 38[/TD]
[TD]NA[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[/TR]
</tbody>[/TABLE]

markmzz said:
Hi!

Maybe the suggestion below can helps (for your first layout):

In B26 and copy down and to the right

=COUNTIF(INDEX($C$2:$H$23,,MATCH(B$25,$C$1:$H$1,0)),$A26)

In J26 and copy down and to the right

=IFERROR(INDEX($B$2:$B$23,MATCH(0,INDEX(COUNTIF($I26:I26,$B$2:$B$23)+(MMULT(--($C$2:$H$23=$A26),{1;1;1;1;1;1})=0),),0)),"")

In H26 and copy down

=SUBSTITUTE(TRIM(J26&" "&K26&" "&L26&" "&M26)," ",", ")

A B C D E F G H I J K L M N
1 Name Department Q1 Q2 Q3 Q4 Q5 Q6
2 Howard IT 3 3 3 3 3 3
3 Gill HR 4 4 4 4 4 4
4 Rajesh REWS 4 4 4 4 4 4
5 Jones MI 4 4 4 4 4 4
6 Sorvino IT 5 5 5 5 5 5
7 Ravi HR 1 1 1 1 1 1
8 Sumit REWS 3 3 3 3 3 3
9 Jardine MI 1 1 1 1 1 1
10 Smith IT 2 2 2 2 2 2
11 Suraj HR 3 3 3 3 3 3
12 Morgan REWS 2 2 2 2 2 2
13 Sameer MI 2 2 2 2 2 2
14 Andrews IT 2 2 2 2 2 2
15 Roshan HR 1 1 1 1 1 1
16 Thompson REWS 4 4 4 4 4 4
17 Mukesh MI 3 3 3 3 3 3
18 Rajan IT 3 3 3 3 3 3
19 Nilesh HR 3 3 3 3 3 3
20 Kivell REWS 2 2 2 2 2 2
21 Mohit MI 1 1 1 1 1 1
22 Manoj IT 3 3 3 3 3 3
23 Mihir HR 2 2 2 2 2 2
24
25 Ratings Q1 Q2 Q3 Q4 Q5 Q6 Department Names HelpCol01 HelpCol02 HelpCol03 HelpCol04
26 1 4 4 4 4 4 4 HR, MI HR MI
27 2 6 6 6 6 6 6 IT, REWS, MI, HR IT REWS MI HR
28 3 7 7 7 7 7 7 IT, REWS, HR, MI IT REWS HR MI
29 4 4 4 4 4 4 4 HR, REWS, MI HR REWS MI
30 5 1 1 1 1 1 1 IT IT
31 6 0 0 0 0 0 0
32
*** ********** *********** **** **** **** **** **** ****************** ** ********** ********** ********** ********** **

<tbody>
</tbody>

Markmzz

need a formula..

VBABEGINER

Well-known Member

Excel Facts

Gerald Higgins

Well-known Member

Arkays

New Member

VBABEGINER

Well-known Member

VBABEGINER

Well-known Member

wideboydixon

Well-known Member

Gerald Higgins

Well-known Member

markmzz

MrExcel MVP

VBABEGINER

Well-known Member

VBABEGINER

Well-known Member

Similar threads

Forum statistics

Share this page

need a formula..

Well-known Member

Excel Facts

Well-known Member

New Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

Well-known 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