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..

0
Utf
4/2/2010 12:50:01 AM
excel.misc 78881 articles. 5 followers. Follow

9 Replies
445 Views

Similar Articles

[PageSpeed] 29

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..
> 
0
Utf
4/2/2010 1:57:01 AM
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..
> > 
0
Utf
4/2/2010 2:38:01 AM
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..

0
Utf
4/2/2010 6:30:01 AM
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..
> 
0
Utf
4/2/2010 7:05:01 AM
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.

0
Utf
4/2/2010 7:37:01 AM
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..
> > 
0
Utf
4/2/2010 8:51:01 AM
> 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
--- 

0
Utf
4/2/2010 2:57:01 PM
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
> --- 
> 
0
Utf
4/5/2010 8:07:01 AM
welcome, glad you did
-- 
Max
Singapore

"kokhong" <kokhong@discussions.microsoft.com> wrote in message 
news:B77A0AF4-C5C3-4933-B405-8A81D1FC89BF@microsoft.com...
> yes, from the information that you given. i manage to get the answer that 
> i
> want.thanks


0
Max
4/5/2010 11:22:07 PM
Reply:

Similar Artilces:

Can anyone suggest an E2k3 server-based product for content-filtering only?
Hello, We have a single Exchange 2003 server on a high-security, completely isolated network supporting about 300 users. I'm looking for a product to install on the Exchange server itself which will quarantine or block messages between users on the server (no SMTP traffic) should they contain specific keywords. Can anyone suggest a product? I'll need an anti-virus scanner as well (with manual updates) so a combined product would be ok. Thanks in advance, - Alan. On 7 Aug 2006 07:53:17 -0700, "Alan" <bruguy@gmail.com> wrote: >Hello, > >We have a single Ex...

Outlook data/address files
I need to reinstall Outlook and I want to copy my address book/archieve files/sent e-mail files etc. where you I find these and what do they look like, ie. extensions. Thanks Look here for all the information you need on backup and restoration of Outlook data: http://www.slipstick.com/config/backup.htm -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. *** In news:07a201c3d580$09e468c0$a101280a@phx.gbl, Rick wrote: > I need to reinstall Outlook and I want to copy my a...

Data from tables
Okay. Think I wasn't clear. Have a table called FDA that contains tons of data. Have table called Log Have table called Daily totals Have a query called Foods_qry The source for the form is the query. The source for the field called Food is the table FDA. This is what I did based on what you said... With Me.RecordsetClone .FindFirst "[Calories] = " & Me.Food If Not .NoMatch Then Me.Bookmark = .Bookmark End If I get an error: Run Time Error '3077' Syntax error (comma) in expression. It points to .FindFirst "[Calori...

compiling tables
Hi I have a long list of names (col D) and a date (col G) when they attended a course. Every person needs to attend a refresher course within 12 months. I am trying to predetermine who will be on the course in 12 months time, plus or minus a week or two, the problem is the course may have 12 persons attending or 6 depending on the number of trainers. Several courses are available on a rolling basis. I guess this is a queuing problem. So far I have worked out the lapse time in days (col J), and the rank (Col K) then I used: =OFFSET(D$11,MATCH(SMALL(K$11:K$38,ROW()-ROW(M$11)+1),K$11:K$38,0)...

Totaling Hits by the hour #3
do you mean a list of all hours i want totaled? like i should create a list of 0-24? -- neuroelectronic ------------------------------------------------------------------------ neuroelectronic's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14720 View this thread: http://www.excelforum.com/showthread.php?threadid=263448 ...

combining data into subtotal report
I need to manually enter data under various categories. (i.e. column A will be Job # and the other columns will contain the counts of various supplies), creating a very large running total of jobs and total supplies. It is important to realize that I may enter the same job # with new data more than one time. Now, I understand how to create subtotals of columns, but I was wondering if, once I have sorted data by job #, there is a way to create a fast and easy report that indicates ONLY the column headings and each job's subtotal. I do not want to see the 25 different entries that it ...

MFC requires C++ compilation (change the compilation setting to
c:\microsoft visual studio\vc98\mfc\include\afx.h(15) : fatal error C1189: #error : MFC requires C++ compilation (use a .cpp suffix) One of my vc6 project has the above error, I check the file, one of my file(myfile.c) include a file util.h which is written in MFC format including .h and .cpp file and all other files are written in c language ,are ..c file and do not have c++ syntax. I compile all the files separately, only the myfile.c have the above error. It seems that I need to change the compilation setting to c++ compile setting to meet the requirement of MFC Class. It seem...

Importing data #2
Importing data into customer, item, suppliers, and GL master files. This is a brand new company and I need to import the master files from another system. I am able to easily dump each file to excel, but how do I import into GP? -- Gino S. GPK Computers Gino, Usually you would use an import tool like Integration Manager or eConnect. If this is a one time import, you're probably better off with Integration Manager. -- Victoria Yudin Dynamics GP MVP "Gino@GPK" <gino@gpk.net.au> wrote in message news:5B6CAB1C-FA16-408A-8242-8B1B9E80B011@microsoft.com... > Impo...

emailing filtered data
Need to know how you can send a filtered email attachment but not have the receipent the ability to unfilter the filtered data. The only data the receipent should get is the filtered information. I put protection on the column, only to find the receipent could advance filter the column. See this example http://www.rondebruin.nl/sendmail.htm#selection My Add-in have this option also -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Janet" <jdromsky@chclc.org> wrote in message news:0b4301c38122$82310e30$a001280a@phx.gbl... > Need to know ho...

Importing Data #3
Good Morning everybody just I would like to ask, How could I import a data from multiple column in an excel sheet to other single column without being any of those imported data duplicated.... PLS HELP ME URGENTLY -- Best Regards Mohamed Hadoka Sphinx Shipping Agency IT Manager & Shipping Excutive Email : Hadoka@gmail.com Msn Messenger : Hadoka@msn.com Yahoo ID : Hadoka1982 ...

chart data by day of week
I have a spreadsheet of >12,000 records of stolen vehicles (cars, SUV's, pickups, motorcycles, etc.) covering a period of almost two years. Each record has a date of theft and other info about the vehicle. I'd like to look at the thefts for day of the seven day week. For instance, I'd like to look at all motorcycle thefts over a one year period and count occurances by day of week and then generate a single chart with theft count as the ordinate and day-of-the-week as the abcissa. I'm new to the group so pardon me if this question has been asked before. Dan wrote: > I h...

Compiler
Is there a compiler available for Access applications, specifically for Access 2003/2007? -- Peter Marshall Manager Information Services Ohio Coatings Company I would be interested in what you are really asking for, but the compiler for Access (all versions) is built-in to the program and is available when you are in the code window by going to DEBUG > COMPILE xxx. There is no external compiler and doesn't need to be. If you are asking if you can make it an exe file, the answer is no. You can use the Access runtime and the developer extensions to create an installation pac...

Data entry form
I need to create a form that will allow me to enter daily sales info from a macro run from a button on the main page of my workbook. I want the results to be added to a list that I can link to other worksheets in the workbook. I am a little lost when it comes to visual basic and was wondering if there is a template I can modify to achieve this task. If not any links to info to achieve this would be helpfull, I have seen many form tutorials and templates, just not one that will accomplish this goal... I'm just guessing but I think what you are looking for is a way to update the range refe...

Reports not returning correct data
I am trying to run the Top Sales Rep Report in HQ and in SO Manager. The problem is they are not returning the same data for each store. The HQ report is also missing some of the Sales Reps. The SO Manager report gives the correct data and all the sales reps. Also, the numbers are off. For instance, Store 1 in SO Manager might show that Rep. A sold $5200 and Store 1 in HQ might show that he only sold $4800. Anyone having any other trouble like this? Please help, my financial person is on my ass!!! Nick Nick, Try this; http://tinyurl.com/eenud Backup first!! Must have access to ...

array data type
Hi, I'm trying to take a column of integers and input them into an arra which I can then use for a loop instead of using this format: If (Worksheets("NeeleyNedPRE").Cells(pRow, pCol).Value = 0) Then For some reason this doesn't work because the column selected al resets to zero rather than running the loop correctly and I think tha an arrray might be the only option. What the easiest way to solve thi problem? -Andre -- Message posted from http://www.ExcelForum.com anjem < wrote: > Hi, I'm trying to take a column of integers and input them into an array > whi...

Specific filter for the data in a field
Hello, In a field there are entries that have the symbol * entered for some reason. For example: DBW*R2 3*FLEW QQ*IF94 How can I filter and find all the data in that field that has "*" entered. I can't seem to find the filter for it. Thanks Dimitris On Wed, 3 Feb 2010 14:29:28 +0200, "Dimitris" <ipackREMOVE@otenet.gr> wrote: Use this expression: Like "*[*]*" This means a wildcard followed by an asterisk followed by a wildcard. -Tom. Microsoft Access MVP >Hello, > >In a field there are entries that have ...

Gathering Data using excel
I have an output from an engineering program that exports data into excel. I would like to have a summary sheet where the pertinent data that I have can be seen instead of having my clients having to pour over hundreds of sheets. How do I get the text to appear. Example Cell A5 shows the text "A/V Alcove - 332" Cell A105 shows the text "Accessible Entrance" On another sheet, I want to put Name in the top row and below that, I want to have it be able to call up Cell A5 and then have A105 below that. There are hundreds of room names so an automatic way ...

Macro to copy data frow rows to one column
With this macro I try to copy data in columns B:M (12 months) and rows 3:8 (6 years) into one column only, to then make a 1-serie graph. Data sample could be this group archive, at the bottom of page: http://groups.google.it/group/microsoft.public.excel/about But I get errors. Thanks for help. DestinationRowID = 10 For RowID = 3 To 8 For ColID = B To M Range((ColID) + CStr(RowID)).Select Selection.Copy Range("B" + CStr(DestinationRowID + 1)).Select ActiveSheet.Paste Application.CutCopyMode = False Next Next One way (I think): ...

compile
Is it possible to somehow compile a program done with excel 2007 ? Thank You Take a look here http://orlando.mvps.org/index.asp#XLtoExe -- __________________________________ HTH Bob "Duplatt" <Duplatt@discussions.microsoft.com> wrote in message news:4ABBCD9E-991C-4848-8E7E-5C1A1F1D2574@microsoft.com... > Is it possible to somehow compile a program done with excel 2007 ? > Thank You THANKS Bob This probably will not make it a 'stand alone' program. I used a program ( Super Calc -- Dos) in the early 80's that had a compiler built in. Worked great fo...

Linking of data
I have one main proposal spreadsheet. In this spreadsheet I have hyperlinks to individual proposal templates (also Excel files) Behind every proposal link I have pricing information from these indiviual proposal templates linked back to my main proposal speadsheet. My problem is that when I open a template and save it under a different name, the pricing information in my main proposal spreadsheet is changed automatically from my individual proposal template to the newly created proposal. Can anybody tell me how to prevent this from happening. The price from the individual proposal temp...

data validation and certain condition
When using data validation, I encountered the following problem. Th sitution is Define data range as list_1, it included A, B, C, D, E, for examle. In cell A5, I can select the desired option, for example A (one of th list_1 data). In cell A10, I input figure here and let B10 (previously set it havin data validation function) is the same value of A5 automatically, but also want to select data from list_1 if necessary, for exampl selecting B. I tried data/validation/custom and input "=if(A10>0,A5,list_1)". th results showed A only but cannot choose another from list_1. Any id...

Libs needing each others to compile.
Hi, I am taking over a project and the first thing I noticed it that the project does not compile properly. if I have 3 .lib files and one .exe file. A.lib needs B.lib but, B.lib needs A.lib to compile. (C.lib needs both A.lib and B.lib). How can this be done? This is obviously wrong, ( I think), but before I make a fool out of myself I just want to check. Is it possible to achieve the above? compile two .lib files that need each others before they even exist? The way they used to compile it was using the previously created lib file to compile. And I guess at some stage they added li...

Macro to delete specific data in tables
Hi: I am an analyst working in service quality for a market research firm. We use SPSS 12 to output the data and later copy paste the tables into Excel for formatting and printing. The problem I currently have is that I got a large group of output tables which come in two different formats. The first table is like this: Satisfacción Expectativas Mean Std Deviation Masculino 5,4 1,4 Femenino 3,8 2,2 Group Total 5,3 1,5 Menos de 2400 UF 5,9 ,9 2401 a 25 mil UF 5,6 1,2 25.001 a 100 mil UF 5,0 1,6 Más de 100.001 UF ...

How to make Visio discard cropped data
The Visio Crop tool appears to be more of a Mask tool. It does not really crop anything. I loaded a large bitmap file into Visio and cropped it down to a small portion in the middle. When I saved and closed the file, I was surprised that the file size did not shrink. When I reopened it, I discovered that I could uncrop the image. Is there some way I can tell Visio to discard the cropped material? I need to extract some small segments from some large graphics files. I don't want to store all that extra junk. Thanks -- Email: Usenet-20031220 at spamex.com (11/09/04) You need to use a r...

Error when trying to import data that was exported on another computer on networ
A file error has occurred in Comma Separated Values (Windows) translator while initializing a translator to build a field map. The file \\Laptop\claptop\documents and settings\All.... is locked. The disk is locked, the file is a read-only, in use by another program, or because you don't have permission to use a network path. ...