Gathering and adding data from different rows

Hi all,

I run a report at work every day that tells me how many shares of a
company have been sold and from what year these shares were granted to
the participant. The year and the amount of shares are on different
lines. For instance, the year would be on D5 and the share amount would
be on D10. This is a daily function and the data changes every day. I am
required to manually scan these reports and add up the shares for each
year separately. I have to separate the amounts for 2003, 1997 and
1996. 

I want to create a spreadsheeet that I can dump this data into and have
the spreadsheet find the years and add up the share amounts for those
specific transactions. Is there a tutorial or anyone that can help me
with this. Like I said, the data is always in column D. The year and
the share amounts are always 5 rows apart from each other. Any help
would be greatly appreciated.

I could also supply an example of the report if needed.

Thanks,
Joel


-- 
rxwillow
------------------------------------------------------------------------
rxwillow's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=5069
View this thread: http://www.excelforum.com/showthread.php?threadid=494606

0
12/19/2005 3:59:55 PM
excel 39879 articles. 2 followers. Follow

2 Replies
395 Views

Similar Articles

[PageSpeed] 48

Hi

This sounds like a job for a Pivot Table. For an introduction, have a read 
of this:
http://www.cpearson.com/excel/pivots.htm
It's really easy to do!

Hope this helps.
Andy.

"rxwillow" <rxwillow.20ao5y_1135008000.8385@excelforum-nospam.com> wrote in 
message news:rxwillow.20ao5y_1135008000.8385@excelforum-nospam.com...
>
> Hi all,
>
> I run a report at work every day that tells me how many shares of a
> company have been sold and from what year these shares were granted to
> the participant. The year and the amount of shares are on different
> lines. For instance, the year would be on D5 and the share amount would
> be on D10. This is a daily function and the data changes every day. I am
> required to manually scan these reports and add up the shares for each
> year separately. I have to separate the amounts for 2003, 1997 and
> 1996.
>
> I want to create a spreadsheeet that I can dump this data into and have
> the spreadsheet find the years and add up the share amounts for those
> specific transactions. Is there a tutorial or anyone that can help me
> with this. Like I said, the data is always in column D. The year and
> the share amounts are always 5 rows apart from each other. Any help
> would be greatly appreciated.
>
> I could also supply an example of the report if needed.
>
> Thanks,
> Joel
>
>
> -- 
> rxwillow
> ------------------------------------------------------------------------
> rxwillow's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=5069
> View this thread: http://www.excelforum.com/showthread.php?threadid=494606
> 


0
Andy
12/19/2005 4:05:53 PM
If I understand your explanation correctly, you have different types o
data in the same column (D).  Pivot tables must have the same type o
data in the same column.  Do you have column headings or row headings
or neither?  You might try oganizing your data in columns such as
Shares in one column, years in another column, company in a thir
column, participant in a 4th column. etc.  A sample of your sheet woul
be helpful if the above is not clear.

JAV

--
JAV
-----------------------------------------------------------------------
JAVB's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2930
View this thread: http://www.excelforum.com/showthread.php?threadid=49460

0
12/21/2005 4:56:03 PM
Reply:

Similar Artilces:

trying to return data with only the latest date
when i run this i get data for the last day. i'm trying to get it to return only for the latest date. for example the instance name column contains all the drive letters. i would like it to return one row for each machinename and drive letter. tried adding distinct but didn't work with free_hd_space_cte as ( select a.counterid, substring(a.machinename,3,20) as machinename, a.objectname, a.countername, a.instancename, b.countervalue, convert(datetime, substring(b.counterdatetime,1, 16)) as TimeRead from counterdetails a inner join counterdata b on a.counterid = b.counter...

Multiple Instance of Same code
I have a form that lists duplicate Batch Job Names (form is based on a table that has job name and job nbr as primary key). I want to give the user the ability to click on the batch job name and open a new form (Batch Job Steps) that will show the job steps for that batch job and if needed click on the duplicate job name and show the job steps. In other words: Batch Job A (12 Job Steps) & Batch Job A (16 Job Steps). Both of these records have job nbr that form part of the primary key but is hidden from the user.) I was trying to use the code below and filter the form on load...

How do I change a data label on an xy scatter to a unique name as.
The only way I have figured out is to create a separate series for each data point, which is tedious for 100+ points...any ideas? Hi, Try one of these free addin to link cells to data labels. This way you can have a single data series rather than multiples. Rob Bovey's Chart Labeler, http://appspro.com John Walkenbach's Chart Tools, http://j-walk.com Cheers Andy SOSCIENT wrote: > The only way I have figured out is to create a separate series for each data > point, which is tedious for 100+ points...any ideas? -- Andy Pope, Microsoft MVP - Excel http://www.andyp...

Filtered Rows using Mode Function?
Hi Excel Forum, I am using numeric "filtered" data and I need to find the most frequen / re-occurring values (1st, 2nd, 3rd, 4th, 5th etc.) from the visibl filtered rows. The Mode function seemed likely, but I cannot get it t work with filtered rows. Can you assist with working examples, please: Formula based input direct on worksheet. VBA Macro using Formula. VBA UserDefined Function. Thank you QT -- Message posted from http://www.ExcelForum.com I would use one of 2 methods :- 1. Formula in another column and sort descending :- =COUNTIF($A$1:$A$20,A16) Need to sort out dupl...

how to create button commande to refresh data in query in excel 2.
I have a excel spreedsheet that contain external data. I would like to put a button in the excel sheet to update the sheet without doing right click and refresh. My user here are very dummies. Jean Francois If this is external data then bringing it in should launch the external data toolbar. They will only need to click the exclamation (!) mark. Seems like re-inventing the wheel. If you must then put a button on the worksheet and assign it to this macro Sub refreshdata() Dim wks As Worksheet Dim qryTab As QueryTable Set wks = ActiveSheet For Each qryTab In wks.QueryTables qryTab.Refr...

MSP_EpmAssignmentByDay
We have quite a lot of projects in Project Server. Should there be data in MSP_EpmAssignmentByDay table on ProjectServer_Reporting db? Now the table is totally empty. How can we get data to that table? ...

How can I get current cell row number
I need to reference the row value of the current cell in a worksheet function: = row() in VBA: activecell.row Hope this helps Rowan excelneophyte wrote: > I need to reference the row value of the current cell ...

List a group of repeated data from sheet1 to sheet2 but not repeat
-- thadi I have one excel sheet1 look like the following. UserName Location Division Software Name Version Maria.Sigmund Parkade Probation Adobe Reader 7.0 Maria.Sigmund Parkade Probation Lotus Notes 6.5.5 Eric.Bell Parkade IT WebFldrs 1.0 Eric.Bell Parkade IT Adobe Reader 7.0 Eric.Bell Parkade IT Symantec AntiVirus 8.1 I want to modify this sheet1 to sheet 2 ...

imported customer data not showing up
After import was done I went into Rms but could not find the customers. What am i doing wrong. This is becoming a nightmare. Did you have errors when importing? Did you include a unique Account Number for each Customer? Did any customers show up? Rob "country" <country@discussions.microsoft.com> wrote in message news:74E7CD37-AD87-4C36-A5AE-6966D4147838@microsoft.com... > After import was done I went into Rms but could not find the customers. > What > am i doing wrong. This is becoming a nightmare. ...

Different formats within the same cell
Hello, i want to enter a word in a cell but only have one of the letter fomatted bold, eg - "wo*r*d" Is this possible -- Message posted from http://www.ExcelForum.com Hi, Yes. Enter your word into the cell, go to the formulae bar, highlight th letter you want to have in bold and then click on the bold icon. This will format just that letter to bold -- Message posted from http://www.ExcelForum.com sure. Just edit the word>highlight the letter(s) and change the font,bold,color etc. will NOT work withing a formula. -- Don Guillett SalesAid Software donaldb@281.com "...

Update Cell Value Based on different Cell
Hi, new to the forum but I've been reading it for a couple of days now. Looking forward to talking and learning with everyone. I think I a better than average with Excel but this problem is driving me nuts! have a list of lottery numbers listed by date drawn (see below fo example) on one worksheet. On another sheet I have a count of how man times a number has been drawn. I would like to add a column to show th last date a number was drawn and have it update automatically when I ad a new drawing. However, I haven't been able to figure out how to ge the date to update automaticall...

Public Folders In different routing/administrative groups
Hi guys! I know this is a real mess, but I have an exchange 2000 org, (not native yet we have some 5.5s out there) with 2 different administrative groups with 2 different routing groups. Each admin/routing group has a 2003 sp2 exchange server running in it right now. One of those 2003 exchange servers has all of our public folders on it, and I would like to replicate those public folders to our other 2003 exchange server in the other administrative/routing group. Is there a reason why I should not do this? Thanks guys! Mike Not at all and probably makes sense from a user perspect...

auto refreshing data in pivot table
hello friends, i am using ms access 2003, vb6. i want a pivot table get refreshed automatically when i open the form. but it is showing me the previous results. so i explicitly needed to refresh manually. how can we refresh data in a pivot table programatically in vb. thanq ...

Pivottables adding datafields not to be "counted"
Hi all! I use a pivottable to make a summary bestseller list Two columns [procuctname and sold]. No problem. The product that sells most at the top, but I would like to add fields to the right of productname like productnumber, productdetails etc. When I do I get all kinds of strange sums. I just want to add the fields for information not sums. Any advanced pivottable flyers out there? TIA /Mats ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** Drag the Field from the Field Table to the ...

Find data in two ranges
Hello, I have a worksheet with in the same row two different ranges: one with nummeric data one with time values. These two ranges works as follow the first nummeric data cooresponds with the first time value, the second nummeric data corresponds with the second time value. For the nummerric data I calculated the best of 3. Now I need the corresponding time value for those best of 3. Thanks for your help with this one. Someone replied to your other post and asked for more detail. -- Biff Microsoft Excel MVP "Santafe" <Santafe@discussions.microsoft.com> w...

Cannot view Calenders on differents Exchange site
We have a rather disperse Exchange ORG. The issue is: We have "ABCompany" as our organization. Email communications is good. We have a hub spoke configuration, Exchange Site "A" and "B" communicate via a X.400 connection through a hub site, lets call it SITE "C". But, user "A" can't view user "B"'s calendar, even though user "B" has given user "A" the rights to view/modify/etc. the calendar element in his mailbox. Both user "A" and "B" belong to different "EXCHANGE SITES&quo...

Why if I insert a blank row in a sheet does the auto complete, for
Please repost your question in the body of a message... In article <EE1E49B1-8DCE-4C83-A753-46AAF8543B6B@microsoft.com>, "Bylin" <Bylin@discussions.microsoft.com> wrote: I have built a spreadsheet that uses the same names over and over but because I have an empty row between each pair of entries, the text doesn't fill in automatically when I start typing in a cell below a blank row. Any ideas? "JE McGimpsey" wrote: > Please repost your question in the body of a message... > > In article <EE1E49B1-8DCE-4C83-A753-46AAF8543B6B@microsoft.c...

Adding a contact in Excel 2003
Hello All, Does anyone Knows how to add a contact to know his / her online presense in Excel 2003 / Word 2003. Any suggestions would be much helpful. Thanks John ...

parse data from text field
I currently have a field in a form that contains the exact path to files stored on my computer (ex. C:\file1\file2\file3\samplefile.doc). The path to the file is different for most of the files. I have changed some things in the database and I would like to parse just the file name and extension (ex. samplefile.doc). I have tried the Mid, Left, Right, and InStr functions but I have not been able to get the desired data. Any help with code to parse the data correctly would be appreciated. The names of files are not equal in length. Thank you accessuser1308, You kinda ...

Question regarding inserting rows and data.
In Sheet1 I have the following (from A1 to C3): 2 3 4 3 4 7 8 8 1 In Sheet2 I have the following (from A1 to C3): =Sheet1!A1+0.001 =Sheet1!B1+0.001 =Sheet1!C1+0.001 =Sheet1!A2+0.001 =Sheet1!B2+0.001 =Sheet1!C2+0.001 =Sheet1!A3+0.001 =Sheet1!B3+0.001 =Sheet1!C3+0.001 ....which means that the values displayed in Sheet2 are the following: 2.001 3.001 4.001 3.001 4.001 7.001 8.001 8.001 1.001 Now, suppose I insert a new row enter new data in Sheet1, like so: 2 3 4 3 4 7 1 1 1 8 8 1 Sheet2 looks the same, and the bottom row's formulae are changed to: =Sheet1!A4+0...

restoring money98 data
I have just reinstalled WindowsXP which meant I had to reinstall Money98. Problem is, I can't open my Money data file which has all my personal bank info from past 10 years and which was backed up on an external harddrive. Error message says the file is 'corrupted or not a Money data file....' If I try to import the file, there is no option for a .mny file. I never used the Money backup system, so that's not an option. I've tried everything I can think of. Any suggestions on how to get at that data? PS: The look of the program and the little Money logo besi...

Using VLOOKUP where data may contain "~" characters
Hello, I'm trying to use VLOOKUP to determine which row in a table corresponds to my data value (or return #N/A if it's not there). All goes fine, except when the data contains a "~" - which appears to be a special escape character as far as Excel is concerned (see example below). As a temporary measure I've replaced "~" with "$" in my data, but I'd like to know if there is a better solution. Result = VLOOKUP(A2,$B$2:$C$6,2,FALSE) Data Lookup Table Result a@a a!a 1 3 a~a 2 a@a 3 a#a 4 a$a 5 -------------------...

Criteria Row
Good afternoon, I have a question problem that I can't seem to solve. It primarily regards the usage of "and" and "or". I have a table that looks like the following: www,4,rrr, 1,2,3,4 xxx,4,eee,0,3,6,7 yyy,4,fff,0,0,0,0 zzz, 4,ddd,40,40,40,40 ggg,4,jjj,4,0,7,0 kkk,4,ddd,51,8,0,9 What I would like to do is select all rows with non zero values less than 50 in the 4th, 5th, 6th , and 7th column. In the above example I would want the www, xxx, zzz, and ggg rows because all the values are not zero and none of the values are greater than 50. I do not want yyy because th...

Data Fields Changing
I have a Master Table in a Master DB linked to a few sub databases. The input forms (to update Master table) are in the sub databases based on queries also in the sub db's. The field names in the Master table change names occasionally. Is there an efficient way to update code in modules, forms, and control/fields in the form(s) when the fields change in the Master table. I can update the control source/fields in the form for the new fld names - but I noticed the old/previous field names still in the dropdown window (at top left of Font window). Also do I have to update a...

Back-Up Data & Delete Data
I am curious, is there a way to back up my current data and then go into RMS Admin, so that I can delete it and start fresh? Would I be able to recover my database once it's been deleted? "Vince" <Vince@discussions.microsoft.com> ha scritto nel messaggio news:7D52BA8C-FF21-4947-94EE-6E9732146984@microsoft.com... >I am curious, is there a way to back up my current data and then go into >RMS > Admin, so that I can delete it and start fresh? Would I be able to > recover > my database once it's been deleted? No... You could backup your current da...