consolidating

I have a situation where a credit card statement comes in and we have to
assign each line item a particular GL code.  On one statement this GL code
may be used numerous times.  The same GL code may also have credits as well
as debits.
The person writes on the statement what each line item will have for a GL
code.
From here we want to enter line by line each item in the statement on a
sheet in Excel.
When this data entry step is done we would like to have Excel do the
consolidation to another sheet so it then does the calculation which should
match the bottom line of the statement.
(pant, pant, pant...)
Oh yea...one more thing...if there are debits and credits for the same code
they need to appear in 2 separate places on the final sheet.

Please help.

Dave French



0
dfrench (14)
7/23/2003 7:48:59 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
728 Views

Similar Articles

[PageSpeed] 53

David

From how you describe it, I would suggest looking at pivot tables. This will
summarise your data for you. If you need to keep credits and debits
separate, I suggest you sor the data by the value column first, and then use
two pivot tables.

Andy
"David French" <dfrench@hopennmachinery.com> wrote in message
news:er0Y3NVUDHA.2196@TK2MSFTNGP11.phx.gbl...
> I have a situation where a credit card statement comes in and we have to
> assign each line item a particular GL code.  On one statement this GL code
> may be used numerous times.  The same GL code may also have credits as
well
> as debits.
> The person writes on the statement what each line item will have for a GL
> code.
> From here we want to enter line by line each item in the statement on a
> sheet in Excel.
> When this data entry step is done we would like to have Excel do the
> consolidation to another sheet so it then does the calculation which
should
> match the bottom line of the statement.
> (pant, pant, pant...)
> Oh yea...one more thing...if there are debits and credits for the same
code
> they need to appear in 2 separate places on the final sheet.
>
> Please help.
>
> Dave French
>
>
>


0
andy9118 (66)
7/24/2003 8:06:57 AM
Reply:

Similar Artilces:

Consolidating Personal Folders
I have somehow opened up 4 personal folders. How do I consolidate all of them into one personal folder and merge all the information. Next, how do I delete the other personal folders? I've read through a couple of books but I can't find the information. Any help would be very much appreciated Thanks MikeSellers Move the contents of the folders to the folder that you want to keep. You can disconnect from the other pst-files by rightclicking the root folder and choose Close. -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook I...

Consolidation Worksheet Data Sequence
I'm consolidating 3 worksheets into one by using Excel 2007 data consolidation. The worksheets have a product number in column A and sales figures in columns B, C & D for Jan, Feb and Mar. The worksheets are in product number sequence. Not all product numbers appear on each sheet, so I consolidate by category using "labels in left column" and "create links to source data" to create a consolidation sheet in outline form. The problem is the consolidated worksheet is not in product number sequence as I need it to be. I have books on Excel 2007 and Excel 2003 and their ...

consolidate queries
I have many too many queries, which I use to gather data for a report. I hope someone can suggest how to consolidate them. For example, 8 of my queries are of the exact same form, except the value of one field is one of 8 possibilities. Here is the SQL for the qCUS query: SELECT q1Main.DReferred, q1Main.CaseType FROM q1Main WHERE (((q1Main.DReferred)>=[Forms]![fgetStats]![tbxBegDate] And (q1Main.DReferred)<=[Forms]![fgetStats]![tbxEndDate]) AND ((q1Main.CaseType)="CUS")); The only difference between the 8 queries is that in the qCUS query, CaseType = "...

Data Consolidation
Biff: Thanks so much for your input on the automation. After a small bit of tweaking, your formula worked like a charm. Now that I have the correlation multi-year table comparison automated, I would like to consolidate each commodities multi-year correlation into an overall correlation set. What I have now is five sets of correlation rows/commodity (representing the five historical data sets) which look like the following: Australian Dollar (AD) Correlations ============================== 1yr: C S PN SI SM 5yr: BO BP CD DX EU GC S SF SM 10yr: DX EU GC MP SF SI W 15yr: DX TU SP MP 20yr: CC G...

customer vendor consolidation #3
I would like to be able to transfer mulitple documents between payables and receivalbles all at once and have the option of having the documents transfer individually or as one lump sum. Currently if mulitple documents are transfered at the same time, a lump sum is transerferred instead of individiuals documents, which mean one credit memo is created instead of one per document being transferred. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in t...

FRx Consolidations #2
I am trying to consolidate two companies with a different account structure. The column format has an account filter and the condolidation is not working. Company One: ##### Company Two: #####-## Column Accout Filter: ????? Note: The first five places of the account in both companies is the same, Company Two has additional departments. When I try to run the consolidation, I can only get one company to tree up, the one whose account structure matches the column account filter. What do I need to do to make the consolidation work? ...

consolidating
I have a situation where a credit card statement comes in and we have to assign each line item a particular GL code. On one statement this GL code may be used numerous times. The same GL code may also have credits as well as debits. The person writes on the statement what each line item will have for a GL code. From here we want to enter line by line each item in the statement on a sheet in Excel. When this data entry step is done we would like to have Excel do the consolidation to another sheet so it then does the calculation which should match the bottom line of the statement. (pant, pant,...

What is the best online consolidator
What is the best online consolidator to produce a single page view of numerous bank and brokerage accounts. I understand that each will remain seperate. On Jun 19, 4:09=A0pm, "WOppenhe" <woppe...@ucla.edu> wrote: > What is the best online consolidator to produce a single page view of > numerous bank and brokerage accounts. =A0I understand that each will rema= in > seperate. I've played with Yodlee for a few years - it keeps getting better and better ...

File consolidation
I have two excel files with workbooks laid out identically. Th wookbooks list all employess in column A and then all days of the year Employees access the file and highlight a day in yellow if they want i off. Unfortunatly two files were set up for the same purpose. I onl want one but employess have been using both. I want to consolidat these files. I basically want to import all the highlights from one t the other. I imagine a macro is in order -- Jon ----------------------------------------------------------------------- Jonl's Profile: http://www.excelforum.com/member.php?action...

Consolidating
Hello All. My company has purchased a smaller company. The smaller company has an exchange 5.5 server and so do we. Is it possible to somehow attach to the other comany's server to migrate the users to our exchange environment once we bring the server over to our company? Any pointers to this type of information would be appreciated. Steve "Steve" <werners@werner.com> wrote: >My company has purchased a smaller company. The smaller >company has an exchange 5.5 server and so do we. Is it >possible to somehow attach to the other comany's server to >...

Consolidating monthly financial calculations
Hello, I'm using A2003 to create an application that, in part, projects financial results over a flexible time period. The following are either input directly to a form, or calculated within a query based on that input: - Reporting period start date (month/year, directly from user input) - Activity Date (day/month/year, directly from user input) - Lag time to first revenue (# of months, directly from user input) - Up-front revenue amount ($, calculated based on other user input) - Up-front revenue date (month/year, calculated based on activity date and lag time) - Recurring revenue am...

Consolidation
hello, I am trying to make a macro in order to consolidate several sheets in several workbooks. Those sheets contains tables with numbers and string of text. That is the reason why I could not use the consolodiation tools. For example : from 3 classeurs containing several sheets... - A.xls contains sheets A1, A2, A3,.. - B.xls contains sheets B1,B2 - C.xls contains sheets C1,C2,C3,C4,.... ....I would like to obtain in the same sheet of a new workbook one table consolidating all the content as below : content of A1 content of A2 content of A3 content of .. content of B1 content of B2 co...

Customer Vendor Consolidation Activation
Exactly which modules are required to be registered to use the Customer/Vendor Transactions within GP? The KB article below indicates I need RM and PM along with the SQL version. I have all that and yet I cannot turn on the functionality. I am able to see this feature on the sample company, but it just does not appear on my live company. I was told that the Refunds Checks module was necessary to make it work...I now have that registered and still no luck. What am I missing? Steve +++++++++++++++++++++++++++++++++++++++++ Using Customer/Vendor Consolidations, you can consolidate ba...

FRx Consolidations #3
I would like to consolidate two companies with a different account structure. The column format has an account filter and the condolidation is not working. Company One: ##### Company Two: #####-## Column Accout Filter: ????? Note: The first five places of the account in both companies is the same, Company Two has additional departments. When I try to run the consolidation, I can only get one company to tree up, the one whose account structure matches the column account filter. What do I need to do to make the consolidation work? Have you looked at the sample consolidation ...

Consolidating??
I have a situation where I need to consolidate several sheets up to one master sheet. Here's the scenario: We have 4 branches for each of the departments to budget for. I'll use the Sales Department in this example. Branch 1 is where Smith and Jones work. Branch 2 is where Johnson and Edwards work. Branch 3 is where Hansen and Justino work. Branch 4 is where Burger and Collins work. The consolidated sheet contains all 8 names. The individual sheets contain only the 2 names that work in that location. There is a code that identifies them on the individual and the consolidated sheet. S...

Consolidation failed
I have a serious problem with DPM 2007. In attempts to fix some hang-up/slowness/agent disconnectivity issues with a specific SQL server on premises - I installed the hotfix rollup for KB 979970. I restarted the server and upgraded all the agents. Things seemed to be running fine, and some of the SQL server problems went away. Backups were running like a champ. Later in the evening, I remote into work and find several SQL databases failing with "Consolidation of recovery points of the replica failed." Its never a whole protection group. Its about 11 separate SQL data...

Customer/Vendor Consolidations for posted transactions?
For anyone using customer/vendor consolidations, is there a setting somewhere that effects consolidating posted transactions? I've established relationships between customer and vendor accounts that both have balances, but on the consolidation window no transactions are shown. At the bottom of that window is a status box that shows 'unposted'. The box is not editable. -- Jim@TurboChef Jim, The Unposted on the bottom is the status of the consolidation, you do not need to change that. If you have performed the module setup for the Customer/Vendor Consolidations (Setup &g...

consolidation
when I use this function, I lose my collaped consolidation window. How do you get it back without starting over? ...

Consolidating sheets
I have a few sheets of data with same header and I would like to put them on the same sheet? What is the easiest way? Try this nc http://www.rondebruin.nl/copy2.htm -- Regards Ron de Bruin http://www.rondebruin.nl "nc" <anonymous@discussions.microsoft.com> wrote in message news:1927c01c44c70$5b05c750$a001280a@phx.gbl... > I have a few sheets of data with same header and I would > like to put them on the same sheet? What is the easiest > way? Hi Ron I tried using your code at your site Sub Test1() Dim sh As Worksheet Dim DestSh As Worksheet Dim...

From where, and how i install Customer/Vendor Consolidation?
Hi >From where, and how i install Customer/Vendor Consolidation? If i Go to Setup >> Company >> i didn't find Customer/Vendor Setup ?! I thing it not installl. So from whear i install it? can i find it in CD ROM? what title name? thank alot On Jun 17, 2:43 pm, Monzer Osama <i...@almonzer.com> wrote: > Hi > > >From where, and how i install Customer/Vendor Consolidation? > > If i Go to Setup >> Company >> i didn't find Customer/Vendor Setup ?! > > I thing it not installl. > > So from whear i install it? can i find it i...

Consolidating??
I have a situation where I need to consolidate several sheets up to one master sheet. Here's the scenario: We have 4 branches for each of the departments to budget for. I'll use the Sales Department in this example. Branch 1 is where Smith and Jones work. Branch 2 is where Johnson and Edwards work. Branch 3 is where Hansen and Justino work. Branch 4 is where Burger and Collins work. The consolidated sheet contains all 8 names. The individual sheets contain only the 2 names that work in that location. There is a code that identifies them on the individual and the consolidated sheet. S...

Consolidate and Lookup
Hi all, I receive 8 reports monthly, 1 for each area, with the all the activity for that period. I would like to consolidate the data into one total sheet. I have a master sheet with all the specialties where I would like to have all the totals show. The activity will be different each month so the the number of rows may change. The number of columns is constant. Below is what I have at the moment... but it cant be re-used as the range is fixed. It also produces an N/A error if one produces an NA. $B7 refers to a specialty code. =3DVLOOKUP($B7,'Herstmere PCT'!$B$5:$N$19,D$5,FALSE)+...

consolidate?
here's what I'm trying to do: I have 1 column with a unique item description in the form of a par number and a column next to it with a quantity. The descriptions ar repeated with different quantities. I would like to have the tota quantity for each part number on 1 row. something like this: part # qty 53359 5 53359 2 53360 4 53360 -2 result: part # qty 53359 7 53360 2 thanks in advance for you help -- Message posted from http://www.ExcelForum.com Read in Help about SUMIF and Pivot Tables; ei...

consolidating
I have a situation where a credit card statement comes in and we have to assign each line item a particular GL code. On one statement this GL code may be used numerous times. The same GL code may also have credits as well as debits. The person writes on the statement what each line item will have for a GL code. From here we want to enter line by line each item in the statement on a sheet in Excel. When this data entry step is done we would like to have Excel do the consolidation to another sheet so it then does the calculation which should match the bottom line of the statement. (pant, pant,...

Customer/Vendor Consolidation customization- Batch
I am new to Great Plains and to Dexterity, but have been a developer for 6+ years using Java, .NET etc. I currently have a project where I need to change the way that GP 8.0 currently does the Customer/Vendor Consolidation process from having to select one vendor/customer at a time, to being able to run a batch that will generate a listing of all current information and display it. Then the user will be able to mark some or all of the entries, and then process them one by one like the application currently does. My client has 3000 plus of these relationships, so this is a big benefit...