SQL in Excel data

Hi all,

Is there a possibility/way to run an SQL query in an excel data sheet?

I have quite some data like the sample below, now i would like to have the 
sum of spending for each person. Like it is possible in Access.

A1         B1
Field1     Field2
Chuck    12,89
Mike       23,09
Jean        9,34
Chuck    30,00
Mike       3,80
Chuck    22,00
Mike       7,23
Jean        10,55
Jean        10,75
Jean        31,45
Chuck    19,99

Result
Field1 SumOfField2
Chuck     84,88
Jean        62,09
Mike       34,12

Advice would be appriciated.

Cheers,
Ludovic 


0
Vsn
10/23/2009 8:57:33 PM
excel 39879 articles. 2 followers. Follow

4 Replies
764 Views

Similar Articles

[PageSpeed] 17

Hi
You could use a formula like this =SUMIF(A2:A600,"Chuck",B2:B600)
adjust range to your need.
HTH
John
"Vsn" <vsn_hotmail_kom> wrote in message 
news:u$aVyNCVKHA.3720@TK2MSFTNGP02.phx.gbl...
> Hi all,
>
> Is there a possibility/way to run an SQL query in an excel data sheet?
>
> I have quite some data like the sample below, now i would like to have the sum 
> of spending for each person. Like it is possible in Access.
>
> A1         B1
> Field1     Field2
> Chuck    12,89
> Mike       23,09
> Jean        9,34
> Chuck    30,00
> Mike       3,80
> Chuck    22,00
> Mike       7,23
> Jean        10,55
> Jean        10,75
> Jean        31,45
> Chuck    19,99
>
> Result
> Field1 SumOfField2
> Chuck     84,88
> Jean        62,09
> Mike       34,12
>
> Advice would be appriciated.
>
> Cheers,
> Ludovic
> 

0
johnd1 (109)
10/23/2009 10:21:16 PM
Good idea, this will work for now. Thanks!


"John" <johnd@newlook.com> schreef in bericht 
news:%23oDsn8CVKHA.4780@TK2MSFTNGP05.phx.gbl...
> Hi
> You could use a formula like this =SUMIF(A2:A600,"Chuck",B2:B600)
> adjust range to your need.
> HTH
> John
> "Vsn" <vsn_hotmail_kom> wrote in message 
> news:u$aVyNCVKHA.3720@TK2MSFTNGP02.phx.gbl...
>> Hi all,
>>
>> Is there a possibility/way to run an SQL query in an excel data sheet?
>>
>> I have quite some data like the sample below, now i would like to have 
>> the sum of spending for each person. Like it is possible in Access.
>>
>> A1         B1
>> Field1     Field2
>> Chuck    12,89
>> Mike       23,09
>> Jean        9,34
>> Chuck    30,00
>> Mike       3,80
>> Chuck    22,00
>> Mike       7,23
>> Jean        10,55
>> Jean        10,75
>> Jean        31,45
>> Chuck    19,99
>>
>> Result
>> Field1 SumOfField2
>> Chuck     84,88
>> Jean        62,09
>> Mike       34,12
>>
>> Advice would be appriciated.
>>
>> Cheers,
>> Ludovic
>>
> 


0
Vsn
10/24/2009 8:56:07 AM
You're welcome,
Sorry I couldn't help with SQL.
Regards
John
"Vsn" <vsn_hotmail_kom> wrote in message 
news:OC4XUfIVKHA.3720@TK2MSFTNGP02.phx.gbl...
> Good idea, this will work for now. Thanks!
>
>
> "John" <johnd@newlook.com> schreef in bericht 
> news:%23oDsn8CVKHA.4780@TK2MSFTNGP05.phx.gbl...
>> Hi
>> You could use a formula like this =SUMIF(A2:A600,"Chuck",B2:B600)
>> adjust range to your need.
>> HTH
>> John
>> "Vsn" <vsn_hotmail_kom> wrote in message 
>> news:u$aVyNCVKHA.3720@TK2MSFTNGP02.phx.gbl...
>>> Hi all,
>>>
>>> Is there a possibility/way to run an SQL query in an excel data sheet?
>>>
>>> I have quite some data like the sample below, now i would like to have the 
>>> sum of spending for each person. Like it is possible in Access.
>>>
>>> A1         B1
>>> Field1     Field2
>>> Chuck    12,89
>>> Mike       23,09
>>> Jean        9,34
>>> Chuck    30,00
>>> Mike       3,80
>>> Chuck    22,00
>>> Mike       7,23
>>> Jean        10,55
>>> Jean        10,75
>>> Jean        31,45
>>> Chuck    19,99
>>>
>>> Result
>>> Field1 SumOfField2
>>> Chuck     84,88
>>> Jean        62,09
>>> Mike       34,12
>>>
>>> Advice would be appriciated.
>>>
>>> Cheers,
>>> Ludovic
>>>
>>
>
> 

0
johnd1 (109)
10/24/2009 11:53:43 AM
A pivot can give you both the listing of unique names & their corresponding 
amounts in a matter of seconds. Create a pivot on your source table, place 
Field1 into ROW, Field2 into DATA (set to Sum). That's it. Check out the 
results in the pivot sheet.
-- 
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---  


0
demechanik (4694)
10/24/2009 7:42:42 PM
Reply:

Similar Artilces:

Excel Adding years or months to a date
I want to be able to add months or years to a date. For example 25/4/05 + 9 months 25/4/05 + 15 years. I am using Excel 2000. The standard way is: =DATE(YEAR(A1),MONTH(A1)+9,DAY(A1)) to add 9 months. For 15 years try: =DATE(YEAR(A1)+15,MONTH(A1),DAY(A1)) HTH Jason Atlanta, GA "Joan" wrote: > I want to be able to add months or years to a date. > > For example 25/4/05 + 9 months > 25/4/05 + 15 years. > > I am using Excel 2000. On Mon, 25 Apr 2005 06:54:03 -0700, "Joan" <Joan@discussions.microsoft.com> wrote: >I want to be able to a...

Deadlocks on SQL Server Error (Repost)
Error: action {64791C4B-0ECF-4D71-B9B1-C558034ECC45} failed in step {727AE8E5-2FE0-476A-9881-81F99AA3C6E5} of process instance {25DCB29B-C813-4A57-A6DA-F06154CB892E}. <description>Invalid code for CRM error</description><details>Transaction (Process ID 172) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. SQL state - 40001, Native error - 1205</details><file>d:\mscrm\build\3017\src\platform\include\omcore\dataacce ss.inl</file><line>325</line> John, I have about 8 incidents...

Dynamic SQL Query and CRM Report parameter
Hello Everyone, Right now I am working on a CRM reporting. I am using SQL server 2005 Reporting services. In my Report I want to pass Field name as a parameter from Report interface. My query is as Following: ------------------------------------------------------ DECLARE @SQL AS nvarchar(1000) DECLARE @Param1 AS nvarchar(50) SET @SQL = 'Select Field1, Field2,'+@Param1+' AS Field3 FROM Table1' ------------------------------------------------------ @Param was difined in 'Report Parameters' and I initialed it. I use Field1,Field2 and Field3 in report layout. I use @...

Excel working in with Outlook
I have Office 2007 so obviously Outlook 2007 and Excel 2007. I have made up a newbie's Excel spreadsheet for my business. One thing to open that has my entire financial year's data in it and my car logbook. One thing I am going to add to it is parts. I want to know when parts need to be re-ordered so will set a level where I want Excel to show up basically something like a "reorder now" warning. However, I am often in a hurry, record the event and don't look at the outcome until I have some time, late at night. At that time, reordering becomes a nightmare. Is ther...

Dynamics 7.0 tables in SQL server 2000
I need to know what should be the content of each of the tables with names starting with "GL" in SQL Server 2000 when using 7.0. Is there a reference that I can go to in Customersource that tells me this information? Thanks. -- Dave Christman System Developer There is an online reference: Tools >> Resource Descriptions >> Tables. Also there is a SDK avaailable on the Great Plains CD's. "Dave Christman" wrote: > I need to know what should be the content of each of the tables with names > starting with "GL" in SQL Server 2000 when...

Split Forms
Is it at all possible to hide the Data Sheet portion of a split form at run time in 2007? The only solution I have found is to play with the SplitFormSize property, but I am afraid that it does not support different display sizes. Any suggestions ? Thanks ...

Excel data disappeared after getting message about compatibility M
I tried to save changes to a spreadsheet, and received the following message: "Compatibility Report for New Customer List.xls Run on 4/6/2010 19:52 The following features in this workbook are not supported by earlier versions of Excel. These features may be lost or degraded when you save this workbook in an earlier file format. Minor loss of fidelity Some cells or styles in this workbook contain formatting that is not supported by the selected file format. These formats will be converted to the closest format available." I clicked OK, because fidelity is not imp...

excel export available
I have a query that I wish to export the results to Excel. However, the Excel button on the Export Data tab is grayed out? It used to work. Any ideas of what makes the Excel Export unavailable? Thanks, Mike ...

Synchronize data across 2 sheets
Hi all Excel 2007 Workbook with a sheet called 2010 around 700 rows/records and a sheet called Previous around 5000 rows records. Both have 31 columns and identical column headers On the 2010 sheet there are records that have changed, how do I get the changed records details onto and overwite the same record on the Previous sheet. Not sure if it would help but each record has a unique reference number. How do i do this ? Hope this makes sense thanks for any help offered Hi If you know which colums are to be changed I would use vlookup function. Vlookup(lookup valu...

Import contacts from Excel
I have numerous contact lists with various categories and wondered if i can import them into an email distribution list so that i do not need to hand type each email? thanks "angie" <angie@discussions.microsoft.com> wrote in message news:64042F86-62E0-4D42-B9CE-49C134DB4B7B@microsoft.com... >I have numerous contact lists with various categories and wondered if i can > import them into an email distribution list so that i do not need to hand > type each email? You can't import into a DL. Instead import into your Contacts and assign categories to ...

Excel is not counting
merry x'mas In excel 2003, i entered a number in a column and dragged down the rows to count the consecutive numbers automatically, but it was just copying the same number instead counting. How to change so as to count? -- Life isa journey not a destination Do the same while holding the CTRL Pressed. Micky "Sherees" wrote: > merry x'mas > In excel 2003, i entered a number in a column and dragged down the rows to > count the consecutive numbers automatically, but it was just copying the same > number instead counting. How to change so as to...

Creating an Excel table from Access
Hi, I have an Access database which I use to log downtime for systems. I have a requirement to produce a monthly report based on this data, however, this needs to be exported to excel in a specific format. Down the left side of the report need to go the names of the services, with the days (numerically like: 1, 2, 3 .. 29, 30 etc) across the top. Then I need to count the number of times each service was down on a give day, and insert that information into the necessary cell - so if intranet services had been down twice on 16th March, for example, there would be a 2 in column 16, whi...

Random Sampling in Microsoft Excell 2002
Hi, I'm trying to create a random sample using the Data Analysis tool in Excell. The problem I'm having is that I can't figure out how to make a non-repeating sample. I want all the observations in the sample to be unique. How do I do this without manually deleting the repeat entries on the output worksheet? next to the entries, put in the formula =Rand() then drag fill down the column. Sort the data and this column with this column as the key. Then take the top "n" items. Regards, Tom Ogilvy "EJ Ford" <edseljoe@earthlink.net> wrote in mess...

Querying Excel data without another program
Hello, I'm an intermediate user of Excel, but I have lots of experience using Access. If you can give me clues on how to handle the information below using only Excel, I'd be grateful! I have a spreadsheet in Excel 97 in which there's a Worksheet named, "Datasource" with a column "B" containing repetitive data. I'd like to create a new worksheet in the same Excel file which shows a single instance of each item used in Column "B". If I were writing the query in SQL, I'd say "SELECT DISTINCT [Column B] FROM [MySpreadsheet]![Datasource...

Excel Formulae #2
Hi, I have a spreadsheet with value in one column & a series of dates in 5 other columns.For each row depending on the value in the first colunm a date may be applicable in one or more of the other columns.e.g 550 in the first column will mean dates will be inserted in the next 2 columns similar to authority levels. My qn. is how can I show what's outstanding if the relevant authority levels have not signed off-basically this is used as a tracker of invoices & I would like to show what is outstanding awaiting approval at anytime. Thanks Raj ----------------------------------...

How to lock a line put on a graph in excell
I am trying to attach a line to a graph with months on the X axis. When I add additional months I want the line to stay in between the original months, however, it always moves as the graph expands. Drawing objects cannot be linked to specific values of chart axes, unless you incorporate them as series in the chart. Try the techniques here: http://peltiertech.com/Excel/Charts/AddLine.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Dennis" <Dennis@discussions.microsoft.com> wrote in message news:336...

Add a Word Document as a Tab in an Excel Document
I work on several documents that require both worksheets and written reports - being able to add a word document as a new tab in Excel would be a neat way of integrating the two into one file for storing/printing/emailing. Maybe... http://www.pcmag.com/article2/0,4149,5224,00.asp "Office Binder: Gone but Not Really" PC Magazine article, January 29, 2002 by M. David Stone on using Binder in Office XP Jim Cone San Francisco, USA "GoDamN" <GoDamN@discussions.microsoft.com> wrote in message news:5F814119-2FA1-4BA6-92EB-C524C8C3820C@microsoft.com... > I work on s...

running excel in background
I'd like to run a program that writes values from one cell to another with a VBA program that is triggered by the clock. I've completed this part but, since I use active cells, you can't run another workbook without the clock activating the cell in the active worksheet. It needs to run in the background all day long. - ideas? will it run in the background if I don't activate cells? Try launching another instance of Excel. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Don" <thedonallen@yahoo.com&g...

how do why update my Excel microsoft office
how do why update my Excel microsoft office If you have office 5, you probably would want to update -- Don Guillett SalesAid Software donaldb@281.com "sam" <sam@discussions.microsoft.com> wrote in message news:2D951DBF-43DE-4C65-8CAC-B4E73DC572FD@microsoft.com... > how do why update my Excel microsoft office ...

How to total cells in a range with data input
Greetings to all members I am running Excel 97. An office colleague presented me with an issue that might be of interest to any Excel whiz and seasoned programmers. I would certainly appreciate any pointers in solving it. So here goes. The set of values includes 33 cells ranging from B4:D14. The data type is numeric. Data is only input in a few cells. QUESTION What would be the function to enter in, say, cell A15 to indicate the total number of cells in range B4:D14 that have received data input? Or does it involve some behind the scene VBA programming? EXAMPLE B6 = 9 C9 = 4 D11 ...

Insert Data From Multiple Worksheets Into One (With Criteria)
Ok, I have created a sales report list that tracks what customers we are contacting, how we are contacting them, if they are a new or existing customer and if we sent a quote, along with some other details. There is a worksheet the salesmen fills out for each day of the month (its linked to a calendar) and that all goes into a "dashboard" that shows the breakdown with graphs. Now what i would like to do is have another worksheet list all the customers that we sent quotes to so they can mark if the quote was won or lost , state sales projections and give reasons why won ...

No Smart Tag help: just a blank "MS Excel Help" window
When I invoke "help on this error" on a Smart Tag drop-down, a blank "MS Excel Help" window appears with no content. The general help pane that appears via <F1> etc. is fine - it's just the Smart Tag help option that doesn't show anything, just a blank window. Any suggestions? Win XP Home SP2 Excel 2003 (11.6355.6360) SP1 ...

How do I export email addresses from excel to outlook?
I am trying to do a mail merge using email via outlook. I have 200+ addresses and I'd like to know how to import the addresses into the contacts section of outlook to do the merge from there. I've tried the help part of out look but it comes up saying that the excel file has no named ranges and that I should use excel to name the range of data to be imported. Any help much appreciated. Thanks Mark In outlook select file/import export/ import from another program or file/ and then follow the instructions from the wizard. Why do you want to do the mailmerge from Outlook? You can...

Email Help: Sending Outlook email from Excel VBA
Hi All, How do I select a specific property in outlook while sending email from excel? There is an option we can set in outlook outgoing emails call "Voting buttons" in that feature there is a custom option that we can select called "Have replies sent to" its a checkbox. I want it checked when i send an email from excel. Hope i made it clear. Thanks in advance If sending through the Outlook Object Model, use the MailItem.VotingOptions property and MailItem.ReplyRecipients collection. -- Dmitry Streblechenko (MVP) http://www.dimastr.com/ OutlookSp...

Creating a word2000 document with access 2000 data
I have a database in access 2000 it is basically customer info. address and dates and timesof appointments. I need to place a button on the formpage that prints a letter using the customer details and the date & time entered in the database. Anyone have any clues as to the easiest way to do this.sort of done it using mailmerge but not what I want. Actually want a word document to pop up that the staff can just double check and press print. Any help would be appreciated even just a point in the right direction to a good source of information. to see if the following website's offer...