need a formula..

V

VBABEGINER

Well-known Member
Joined
Jun 15, 2011
Messages
1,232
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)
 
Sort by date Sort by votes
VBABEGINER said:
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...
Click to expand...

Hi!

Some thing like this?

ABCDEFGHIJKLMNOPQRS
1NameBusiness UnitQ1Q2Q3Q4Q5Q6Q7
2User 1Reins4653645
3User 2FINA5454777
4User 3Comp1089991010
5User 4TA5444545
6User 5GC7565557
7User 6Re5555555
8User 7GB7755878
9User 8WGB1091091099
10User 9GC891097109
11User 10GC8899688
12User 11GB5455678
13User 12Support7866867
14User 13GC75558810
15User 14GB5665756
16User 15GC78887910
17User 31NA85581056
18User 32NA898891010
19User 33NA7780733
20User 34NA77877810
21User 35NA99998810
22User 36NA9777577
23User 37NA989981010
24User 38NA98109599
25
26
27RatingsQ1Q2Q3Q4Q5Q6Q7Department NamesHelpCol01HelpCol02HelpCol03HelpCol04HelpCol05HelpCol06HelpCol07HelpCol08
2810000000
2920000000
3030001011Reins, NAReinsNA
3141312020Reins, FINA, TA, GBReinsFINATAGB
3255476543Reins, FINA, TA, GC, Re, GB, NAReinsFINATAGCReGBNA
3360231312Reins, GC, GB, Support, NAReinsGCGBSupportNA
3477412644FINA, GC, GB, Support, NAFINAGCGBSupportNA
3584643543Comp, GB, GC, Support, NACompGBGCSupportNA
3694447233Comp, WGB, GC, NACompWGBGCNA
37102030247Comp, WGB, GC, NACompWGBGCNA
38
********************************************************
******************************************************************

<tbody>
</tbody>


Markmzz
 
Upvote 0

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
I sincerely Thanks to Gerald, Arkays, Wideboydixon and markmzzz..


Thanks you all for posting and giving me a solution.


I always appriciate MRExcel all master's. And I really appriciate your knowledge now.


You guys are rocks..!!


I have used, Markmzz solution..


So agian thanks for all. Keep posting your help to us..(y)
 
Upvote 0
VBABEGINER said:
I sincerely Thanks to Gerald, Arkays, Wideboydixon and markmzzz..

Thanks you all for posting and giving me a solution.

I always appriciate MRExcel all master's. And I really appriciate your knowledge now.

You guys are rocks..!!

I have used, Markmzz solution..

So agian thanks for all. Keep posting your help to us..(y)
Click to expand...

You are welcome and thanks for the feedback.

Markmzz
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Need a formula to generate a list just type department name
Replies
1
Views
192
Kerryx
K
T
  • Solved
Number of day
Replies
5
Views
296
Tetra201
Tetra201
F
  • Question
VBA to copy and insert a row below only if a certain criteria is met
Replies
2
Views
93
feni1388
F
M
  • Solved
Adding a NR to a formula & changing an IF to an IFS statement.
Replies
3
Views
104
Marvo
M
D
  • Question
If date from table is within date range, then return value from other table
Replies
2
Views
185
DipDip
D

Forum statistics

Threads
1,216,084
Messages
6,128,721
Members
449,465
Latest member
TAKLAM

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top