Remember Me?

#1
June 29th 09, 01:43 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 699
VLOOKUP/MATCH Query - quite complex....but interesting.

I have the following data sets:

table 1 - a number comparison values for different companies and shops
shop1 shop2 shop3 shop4 shop5
0.00015 0.23 0.09 0.1 0.69 vod
0.5 0.00005 0.999 0.26 0.95 tesco
0.93 1.56 0.94 2.5 0.32 c&a
0.0003 0.73 5.69 0.47 0.00001 dixons

I summarise my data below by taking the top 5 largest values and the
correponding companies and shops:

summary table

company shop value
dixons shop3 5.69
c&a shop4 2.5
c&a shop2 1.56
tesco shop3 0.999
tesco shop5 0.95

i now want to add another column to my summary table above by sourcing data
from table 2 at the bottom of the page.

THIS COLUMN
company shop value table 2 value
dixons shop3 5.69 l
c&a shop4 2.5 h
c&a shop2 1.56 etc etc
tesco shop3 0.999
tesco shop5 0.95

Thing is - the summary table will change all the time as i rank the top
5....the data in real life is live...and so i need a formula in the 'table 2
value column' as opposed to just a straight v look up that would look up the
same position everytime....any thoughts?

Thanks to all the seriously bright people who've helped me on this as well.

table 2
shop1 shop2 shop3 shop4 shop5
a b c d e vod
f g h i j tesco
k l m n o c&a
p q r s t dixons

ps. some formula's i've used so far (for other people's benefit)
1. to look up and rank the top 5 biggest companies
=INDEX(\$G\$4:\$G\$7,MAX((\$B\$4:\$F\$7=LARGE(\$B\$4:\$F\$7,RO W(B1:G1)))*ROW(\$B\$4:\$F\$7)-MIN(ROW(\$B\$4:\$F\$7))+1))

2. to look up and rank the top 5 biggest corresponding shops
=INDEX(\$B\$3:\$F\$3,MAX((\$B\$3:\$F\$7=LARGE(\$B\$3:\$F\$7,RO W(B1)))*COLUMN
(\$B\$3:\$F\$7)-MIN(COLUMN(\$B\$3:\$F\$7))+1))

3. to look up and rank the corresponding top 5 biggest values that go with
the above information
=LARGE(B\$4:F\$7,ROWS(\$3:3))

#2
June 29th 09, 01:46 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 699
VLOOKUP/MATCH Query - quite complex....but interesting.

"l" and "h" should actually be:

company shop value table 2 value
dixons shop3 5.69 r
c&a shop4 2.5 n
c&a shop2 1.56 etc etc
tesco shop3 0.999
tesco shop5 0.95

"SAM" wrote:

I have the following data sets:

table 1 - a number comparison values for different companies and shops
shop1 shop2 shop3 shop4 shop5
0.00015 0.23 0.09 0.1 0.69 vod
0.5 0.00005 0.999 0.26 0.95 tesco
0.93 1.56 0.94 2.5 0.32 c&a
0.0003 0.73 5.69 0.47 0.00001 dixons

I summarise my data below by taking the top 5 largest values and the
correponding companies and shops:

summary table

company shop value
dixons shop3 5.69
c&a shop4 2.5
c&a shop2 1.56
tesco shop3 0.999
tesco shop5 0.95

i now want to add another column to my summary table above by sourcing data
from table 2 at the bottom of the page.

THIS COLUMN
company shop value table 2 value
dixons shop3 5.69 l
c&a shop4 2.5 h
c&a shop2 1.56 etc etc
tesco shop3 0.999
tesco shop5 0.95

Thing is - the summary table will change all the time as i rank the top
5....the data in real life is live...and so i need a formula in the 'table 2
value column' as opposed to just a straight v look up that would look up the
same position everytime....any thoughts?

Thanks to all the seriously bright people who've helped me on this as well.

table 2
shop1 shop2 shop3 shop4 shop5
a b c d e vod
f g h i j tesco
k l m n o c&a
p q r s t dixons

ps. some formula's i've used so far (for other people's benefit)
1. to look up and rank the top 5 biggest companies
=INDEX(\$G\$4:\$G\$7,MAX((\$B\$4:\$F\$7=LARGE(\$B\$4:\$F\$7,RO W(B1:G1)))*ROW(\$B\$4:\$F\$7)-MIN(ROW(\$B\$4:\$F\$7))+1))

2. to look up and rank the top 5 biggest corresponding shops
=INDEX(\$B\$3:\$F\$3,MAX((\$B\$3:\$F\$7=LARGE(\$B\$3:\$F\$7,RO W(B1)))*COLUMN
(\$B\$3:\$F\$7)-MIN(COLUMN(\$B\$3:\$F\$7))+1))

3. to look up and rank the corresponding top 5 biggest values that go with
the above information
=LARGE(B\$4:F\$7,ROWS(\$3:3))

#3
June 29th 09, 03:06 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Apr 2007 Posts: 11,501
VLOOKUP/MATCH Query - quite complex....but interesting.

Sam,

I'm being a bit slow on the uptake here but why does the r appear, what is
r?, what are the rules?

Mike

dixons shop3 5.69 r

"SAM" wrote:

"l" and "h" should actually be:

company shop value table 2 value
dixons shop3 5.69 r
c&a shop4 2.5 n
c&a shop2 1.56 etc etc
tesco shop3 0.999
tesco shop5 0.95

"SAM" wrote:

I have the following data sets:

table 1 - a number comparison values for different companies and shops
shop1 shop2 shop3 shop4 shop5
0.00015 0.23 0.09 0.1 0.69 vod
0.5 0.00005 0.999 0.26 0.95 tesco
0.93 1.56 0.94 2.5 0.32 c&a
0.0003 0.73 5.69 0.47 0.00001 dixons

I summarise my data below by taking the top 5 largest values and the
correponding companies and shops:

summary table

company shop value
dixons shop3 5.69
c&a shop4 2.5
c&a shop2 1.56
tesco shop3 0.999
tesco shop5 0.95

i now want to add another column to my summary table above by sourcing data
from table 2 at the bottom of the page.

THIS COLUMN
company shop value table 2 value
dixons shop3 5.69 l
c&a shop4 2.5 h
c&a shop2 1.56 etc etc
tesco shop3 0.999
tesco shop5 0.95

Thing is - the summary table will change all the time as i rank the top
5....the data in real life is live...and so i need a formula in the 'table 2
value column' as opposed to just a straight v look up that would look up the
same position everytime....any thoughts?

Thanks to all the seriously bright people who've helped me on this as well.

table 2
shop1 shop2 shop3 shop4 shop5
a b c d e vod
f g h i j tesco
k l m n o c&a
p q r s t dixons

ps. some formula's i've used so far (for other people's benefit)
1. to look up and rank the top 5 biggest companies
=INDEX(\$G\$4:\$G\$7,MAX((\$B\$4:\$F\$7=LARGE(\$B\$4:\$F\$7,RO W(B1:G1)))*ROW(\$B\$4:\$F\$7)-MIN(ROW(\$B\$4:\$F\$7))+1))

2. to look up and rank the top 5 biggest corresponding shops
=INDEX(\$B\$3:\$F\$3,MAX((\$B\$3:\$F\$7=LARGE(\$B\$3:\$F\$7,RO W(B1)))*COLUMN
(\$B\$3:\$F\$7)-MIN(COLUMN(\$B\$3:\$F\$7))+1))

3. to look up and rank the corresponding top 5 biggest values that go with
the above information
=LARGE(B\$4:F\$7,ROWS(\$3:3))

#4
June 29th 09, 03:40 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 699
VLOOKUP/MATCH Query - quite complex....but interesting.

sure,

i fyou look at table 2 you'll see that it is the same size as table 1. the r
and n are just the letters in the equivalent position in tabel 2.

e.g. 5.69 is the highest value in table 1; shop 3, company: dixons.

in table 2, shop 3 and ixons give letter r....so i would want to report
letter r.

"Mike H" wrote:

Sam,

I'm being a bit slow on the uptake here but why does the r appear, what is
r?, what are the rules?

Mike

dixons shop3 5.69 r

"SAM" wrote:

"l" and "h" should actually be:

company shop value table 2 value
dixons shop3 5.69 r
c&a shop4 2.5 n
c&a shop2 1.56 etc etc
tesco shop3 0.999
tesco shop5 0.95

"SAM" wrote:

I have the following data sets:

table 1 - a number comparison values for different companies and shops
shop1 shop2 shop3 shop4 shop5
0.00015 0.23 0.09 0.1 0.69 vod
0.5 0.00005 0.999 0.26 0.95 tesco
0.93 1.56 0.94 2.5 0.32 c&a
0.0003 0.73 5.69 0.47 0.00001 dixons

I summarise my data below by taking the top 5 largest values and the
correponding companies and shops:

summary table

company shop value
dixons shop3 5.69
c&a shop4 2.5
c&a shop2 1.56
tesco shop3 0.999
tesco shop5 0.95

i now want to add another column to my summary table above by sourcing data
from table 2 at the bottom of the page.

THIS COLUMN
company shop value table 2 value
dixons shop3 5.69 l
c&a shop4 2.5 h
c&a shop2 1.56 etc etc
tesco shop3 0.999
tesco shop5 0.95

Thing is - the summary table will change all the time as i rank the top
5....the data in real life is live...and so i need a formula in the 'table 2
value column' as opposed to just a straight v look up that would look up the
same position everytime....any thoughts?

Thanks to all the seriously bright people who've helped me on this as well.

table 2
shop1 shop2 shop3 shop4 shop5
a b c d e vod
f g h i j tesco
k l m n o c&a
p q r s t dixons

ps. some formula's i've used so far (for other people's benefit)
1. to look up and rank the top 5 biggest companies
=INDEX(\$G\$4:\$G\$7,MAX((\$B\$4:\$F\$7=LARGE(\$B\$4:\$F\$7,RO W(B1:G1)))*ROW(\$B\$4:\$F\$7)-MIN(ROW(\$B\$4:\$F\$7))+1))

2. to look up and rank the top 5 biggest corresponding shops
=INDEX(\$B\$3:\$F\$3,MAX((\$B\$3:\$F\$7=LARGE(\$B\$3:\$F\$7,RO W(B1)))*COLUMN
(\$B\$3:\$F\$7)-MIN(COLUMN(\$B\$3:\$F\$7))+1))

3. to look up and rank the corresponding top 5 biggest values that go with
the above information
=LARGE(B\$4:F\$7,ROWS(\$3:3))

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post robottv Excel Discussion (Misc queries) 0 April 10th 08 01:58 PM karlsven Excel Worksheet Functions 2 December 20th 07 08:18 AM Hammond-Wandsworth Excel Worksheet Functions 3 August 31st 07 03:42 PM Jennifer Reitman Excel Discussion (Misc queries) 3 August 10th 06 08:51 PM Stacy Excel Worksheet Functions 0 June 30th 05 09:50 PM

All times are GMT +1. The time now is 03:36 AM.