Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
compile data base on hit
Before compile the data is:
IP user name Hits x A 10 y A 1000 Z A 20 L B 2000 M B 10 N B 500 After compile the data is: IP user name Hits y A 1000 L B 2000 are there any script can choose the username (Column B) base on the highest hits at columns C. Thank you.. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
compile data base on hit
Assume your data as posted in cols A to D, where
col A = misc letters, col B = items, eg A, B, col D = numbers Assume listed in G2 down are the unique items from col B: A, B, etc. Then In H2, array enter (press CSE): =MAX(IF($B$2:$B$10=G2,$D$2:$D$10)) In I2, array enter (press CSE): =INDEX($A$2:$A$10,MATCH(H2,IF($B$2:$B$10=G2,$D$2:$ D$10),0)) Copy H2:I2 down. H2 returns the desired maximum number from col D for the unique item listed in G2, I2 returns the corresponding misc letter from col A. CSE means to press CTRL+SHIFT+ENTER to confirm the formula. Visually check the formula bar for the curlies: { } inserted by Excel which confirms that the CSE was properly done. If you don't see the { }, re-do the CSE. Inspiring? hit YES below -- Max Singapore --- "kokhong" wrote: Before compile the data is: IP user name Hits x A 10 y A 1000 Z A 20 L B 2000 M B 10 N B 500 After compile the data is: IP user name Hits y A 1000 L B 2000 are there any script can choose the username (Column B) base on the highest hits at columns C. Thank you.. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
compile data base on hit
Max, i only have three line here, there are A, B, C columns. A is IP, B is
username, and C is hits. I need the formula which can return IP and unique username with highest hit..From ur example, it is not match with mine, i do not hav G2.Thank in ur advance.. "Max" wrote: Assume your data as posted in cols A to D, where col A = misc letters, col B = items, eg A, B, col D = numbers Assume listed in G2 down are the unique items from col B: A, B, etc. Then In H2, array enter (press CSE): =MAX(IF($B$2:$B$10=G2,$D$2:$D$10)) In I2, array enter (press CSE): =INDEX($A$2:$A$10,MATCH(H2,IF($B$2:$B$10=G2,$D$2:$ D$10),0)) Copy H2:I2 down. H2 returns the desired maximum number from col D for the unique item listed in G2, I2 returns the corresponding misc letter from col A. CSE means to press CTRL+SHIFT+ENTER to confirm the formula. Visually check the formula bar for the curlies: { } inserted by Excel which confirms that the CSE was properly done. If you don't see the { }, re-do the CSE. Inspiring? hit YES below -- Max Singapore --- "kokhong" wrote: Before compile the data is: IP user name Hits x A 10 y A 1000 Z A 20 L B 2000 M B 10 N B 500 After compile the data is: IP user name Hits y A 1000 L B 2000 are there any script can choose the username (Column B) base on the highest hits at columns C. Thank you.. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
compile data base on hit
The uniques list was one of my stated assumptions. You may already have a
ready list of all usernames, no? If not, you could use eg: advanced filter on col B (check: uniques) to list out the uniques from col B (ie unique usernames) into an empty col to the right of your source data, then copy that uniques list n paste special as values into G2 down. Then apply the array formulae given earlier - which address what I thought was more critical in your query, adjusting the ranges to suit your actual data extents. -- Max Singapore --- "kokhong" wrote: Max, i only have three line here, there are A, B, C columns. A is IP, B is username, and C is hits. I need the formula which can return IP and unique username with highest hit..From ur example, it is not match with mine, i do not hav G2.Thank in ur advance.. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
compile data base on hit
Max, the advance filter option cannot filter only a row to unique record..a
message error will pop out if i do so. "Max" wrote: The uniques list was one of my stated assumptions. You may already have a ready list of all usernames, no? If not, you could use eg: advanced filter on col B (check: uniques) to list out the uniques from col B (ie unique usernames) into an empty col to the right of your source data, then copy that uniques list n paste special as values into G2 down. Then apply the array formulae given earlier - which address what I thought was more critical in your query, adjusting the ranges to suit your actual data extents. -- Max Singapore --- "kokhong" wrote: Max, i only have three line here, there are A, B, C columns. A is IP, B is username, and C is hits. I need the formula which can return IP and unique username with highest hit..From ur example, it is not match with mine, i do not hav G2.Thank in ur advance.. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
compile data base on hit
Select the entire username col before you do the advanced filter uniques
-- Max Singapore --- "kokhong" wrote: Max, the advance filter option cannot filter only a row to unique record..a message error will pop out if i do so. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
compile data base on hit
after filter with uniques unit and copy the columns B to G, the original B
columns need to filter it to all, or keep it in uniques value? and since i dun hav data in column D but C should i edit the formula lik below? and since i have 14706 row, should it be below formula? In H2, array enter (press CSE): =MAX(IF($B$2:$B$14706=G2,$C$2:$C$14706)) In I2, array enter (press CSE): =INDEX($A$2:$A$10,MATCH(H2,IF($B$2:$B$14706=G2,$C$ 2:$C$14706),0)) "Max" wrote: Assume your data as posted in cols A to D, where col A = misc letters, col B = items, eg A, B, col D = numbers Assume listed in G2 down are the unique items from col B: A, B, etc. Then In H2, array enter (press CSE): =MAX(IF($B$2:$B$10=G2,$D$2:$D$10)) In I2, array enter (press CSE): =INDEX($A$2:$A$10,MATCH(H2,IF($B$2:$B$10=G2,$D$2:$ D$10),0)) Copy H2:I2 down. H2 returns the desired maximum number from col D for the unique item listed in G2, I2 returns the corresponding misc letter from col A. CSE means to press CTRL+SHIFT+ENTER to confirm the formula. Visually check the formula bar for the curlies: { } inserted by Excel which confirms that the CSE was properly done. If you don't see the { }, re-do the CSE. Inspiring? hit YES below -- Max Singapore --- "kokhong" wrote: Before compile the data is: IP user name Hits x A 10 y A 1000 Z A 20 L B 2000 M B 10 N B 500 After compile the data is: IP user name Hits y A 1000 L B 2000 are there any script can choose the username (Column B) base on the highest hits at columns C. Thank you.. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
compile data base on hit
after filter with uniques unit and copy the columns B to G, the original B
columns need to filter it to all, or keep it in uniques value? Copy the uniques n paste it into G2 down. I did not say to delete or overwrite col B and since i dun hav data in column D but C should i edit the formula lik below? and since i have 14706 row, should it be below formula? Yes, of course, change it to suit As for the adaptations, this index bit INDEX($A$2:$A$10 needs to be changed as well to INDEX($A$2:$A$14706 Don't forget to ensure that the CSE bit is done properly. -- Max Singapore --- |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
compile data base on hit
yes, from the information that you given. i manage to get the answer that i
want.thanks "Max" wrote: after filter with uniques unit and copy the columns B to G, the original B columns need to filter it to all, or keep it in uniques value? Copy the uniques n paste it into G2 down. I did not say to delete or overwrite col B and since i dun hav data in column D but C should i edit the formula lik below? and since i have 14706 row, should it be below formula? Yes, of course, change it to suit As for the adaptations, this index bit INDEX($A$2:$A$10 needs to be changed as well to INDEX($A$2:$A$14706 Don't forget to ensure that the CSE bit is done properly. -- Max Singapore --- |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
compile data base on hit
welcome, glad you did
-- Max Singapore "kokhong" wrote in message ... yes, from the information that you given. i manage to get the answer that i want.thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I compile data? | Excel Discussion (Misc queries) | |||
compile data from CSV | Excel Discussion (Misc queries) | |||
compile data | Excel Discussion (Misc queries) | |||
how do I print data in a spreadsheet data base to a mailing lable. | Excel Discussion (Misc queries) | |||
how do I print data in a spreadsheet data base to a mailing lable. | Excel Discussion (Misc queries) |