Sales Ledger Formatting - Insert Item Description Without Cut / Paste 25k Rows?
Sep 17, 2013
I'm working from a sales ledger file whereby I want all invoice data in one row although the Item Number & Item Description are on the first row. Rows 2-10 (for example) are the actual details of the invoice: Invoice #; Date; Customer ID; Customer Name; QTY; etc.
Is there a formula or easier way than copying/pasting the "Item Description" to the first column (J in this case) without any related invoice data?
View 2 Replies
ADVERTISEMENT
Mar 20, 2014
I've got 2 columns.
Column A, Item Description
Column B, Item Cost
In Column B, it will display the item cost as either a numerical value, (example $1.00) or it will say "disco" for discontinued.
I want to update the item description in Column A by adding the work "disco" if Column B display's "disco". If column B display's any numerical value (0-999999999) then I would it to do nothing.
what IF formula I need to accomplish this?
View 6 Replies
View Related
Apr 20, 2012
I have a very large spreadsheet with daily sales for over 140 different items, over the past 2 years.
This is how my data looks like:
ITEM NAME 4/1/12 4/2/12 4/3/12 etc
item1 10 15 500
item2 5 0 3
item3 0 0 5
etc
I'm trying to build a formula that allows me to see which item is the highest trending over a period of time I input.
I figured out the way how to select data between specific Start and End dates, but don't know how to interpret "trending" in a formula.
I was thinking maybe highest jump from beginning to end of interval, but what if it's a new item that was launched halfway through the interval and does poorly throughout, that would still be "trending" under that rationale....
View 1 Replies
View Related
Apr 14, 2009
I have a macro that inserts a blank column for every sheet in the workbook. The problem i have is that i only want a limited number of columns inserted. I only want a column to be inserted if the sheet tab has the name "detail" in it. I have tried for hours to get this to work, but i keep getting errors. Here is the code i am using.
View 4 Replies
View Related
Jan 21, 2008
I have a table I down load from SAP with the following headers;
Route, Description, DChl, YF21 (SD),YF31 (SD),YF21 (MM),YF31 (MM),YF10 (MM),YF11 (MM),YF12 (MM).
All I need to do is look up and match the Description and then insert the value (they are percentages) found under the columes YF21 onwards.
I have tried VLOOKUP until I am blue in the face and cannot get it to work.
View 9 Replies
View Related
Aug 24, 2008
I have a list of data in column A. This is grouped by a set of key information and then subsets. The beginning of the key data is signified by the cell colour blue and then the subsets signified by the colour yellow. I want to insert a row just below the colour blue and enter the value " Header" and then when each colour is yellow, insert a row and enter "Subset 1" for the first and then "Subset 2" for the second etc.. The number of subsets can vary.
ABC Customer (Colour Blue)
Info
Info
Product 1 (Yellow)
Info
Product 2 (Yellow)
Info
Info
Product 3 (Yellow)
Info
XYZ Customer (Colour Blue)
Info
Product 1 (Yellow)
Info
Info
Product 2 (Yellow)
Info
Should be:..................
View 5 Replies
View Related
May 15, 2013
Doc1.docx
I need the Left picture to look like the Right picture... There's over 30,000 lines of data.
View 3 Replies
View Related
Nov 22, 2008
I need a macro that will insert 2 columns to the right from Col H, add header value "Ledger" in Col I1 and "Statement" in Col J1. I then need the macro to put the values for ledger and statement next to each other as they are both related (as per below screen shot). I have highlighted which units are related so you can see that all related entries will have Ledg followed by Stmt and if they are not related then will be inputted under their relevant headings. I then want the macro to delete rows which have blank cells.
Ledg StmtUnitsLedgerStatementLedg389,351.00389,351.00599,811.00Stmt599,811.00Ledg148,822.00148,822.00209,536.00Stmt209,536.00Ledg1,674,562.001,674,562.001,661,564.00Stmt1,661,564.00Stmt111,110.000.00111,110.00Stmt7,738.000.007,738.00Ledg39,164.0039,164.000.00Ledg197,088.00197,088.000.00Ledg195,525.00195,525.000.00Ledg8,859.008,859.008,860.00Stmt8,860.00Ledg90,000.0090,000.00138,648.00Stmt138,648.00Ledg717717.000.00Ledg11.000.00Ledg5,314,520.005,314,520.000.00Ledg2,096,835.002,096,835.002,402,199.00Stmt2,402,199.00Ledg114,797.00114,797.00193,797.00Stmt193,797.00
View 9 Replies
View Related
Aug 7, 2008
I am having difficulty in filtering information from a table.
Essentially, I would first like to filter by same Expiry Dates (Column D). Then based on these same dates, filter by keywords (Column A) within text of those common dates.
The outcome should display entries that have "same expiry date: (Column D) and key word in common - e.g. common word may be "Fleet" (from Column A)
Attached spreadsheet explains further (2 worksheets).
View 14 Replies
View Related
Apr 4, 2012
I'm trying to add an item to a list box in alphabetical order.
Adding them at the end is easy, but is there an easy way to add an item part-way through a list?
THis is what I have so far..
Code:
'goes somewhere else
For Count = 0 To lstEmployees.ListCount - 1
If cmbAmendmentsEmployees.List(cmbAmendmentsEmployees.ListIndex, 0) < lstEmployees.List(Count) Then
[Code].....
View 1 Replies
View Related
Aug 8, 2009
On the attached Excel file, I have code that will insert a variable number of rows and copy and paste from and to variable positions. That all works fine when run from a command button, but when I try to run it from the Worksheet_Calculate by entering 1 in J1 or K1 (inrange cell is J1+K1 for testing purposes) the CommandButton1_Click sub runs continously until an error occurs.
View 4 Replies
View Related
Jan 5, 2012
I have an excel sheet which has 70 sheets in it...All of them are numbered but not in a sequential manner (eg : 210,211,201,202,215 etc..).The numbering is nothing but the sales order number....
All the sheets have same data (i.e Column A contains Product Sold, Column B contains Sale Value)...The thing is i want to consolidate all the data in sales order number wise. i.e.
Column A Column B
201 $200
202 $300
203 $450
View 8 Replies
View Related
Jul 22, 2013
Using excel 2007 I've created a new tab and wanted to place the 2003 file menu on it. I don't know how to include it automatically in the tab using a combination of XML and VBA. So I created a button with the callback to run some code. Unfortunately, it still places the item in the Add-in tab. How can I get the undernoted code to operate within my new tab with either XLM or VBA?
Code:
Sub InsertFileMenu2003()
Dim cb As CommandBar
Set cb = Application.CommandBars.Add("xl2003 Menu", , True)
CommandBars("Built-in Menus").Controls("&File").Copy cb
Application.CommandBars("xl2003 Menu").Visible = True
End Sub
View 6 Replies
View Related
Jul 2, 2009
I am trying to determine the top contributors to 50% of sales based on cumulative percent of sales (see attached file). I can determine if percent of sales is less than 50%, but I need to include the person that pushes the group of top performers over the 50% mark.
View 3 Replies
View Related
Feb 4, 2013
I wanted to get the data from data sheet where i have entered the data manually for sales what have done
Now I wanted is when i enter date i should get the product and price in other sheet
Example:
If I enter date in a1 cell
I should get the detail of product in b1-b10(if i sold 10 item )which is in data sheet
And c1 - c10 price (if i sold 10 item @that price)
View 4 Replies
View Related
Feb 20, 2008
See the attachment. I want the percentage of Car Sales to total sales of different countries automatically.
View 2 Replies
View Related
Oct 17, 2013
I have a sales level that I need to track...My rolling 12 months' sales must be $85,000 and my currently monthly sales must be $7,000. I have a sheet that tracks the $85,000 and tells me what I need to achieve that, but I haven't figured out how to include the $7,000 monthly minimum....
The chart below is what I have. So for example, this month it's telling me I only need to sell another 3016.46 to hit the $85,000 rolling 12, but I actually need to hit $4821.79 to meet the $7k minimum.
Actual Rolling 12 Goal
Sep 2012 5,367.24 73,663.30
Oct 2012 5,649.93 69,496.28
Nov 2012 14,163.38 73,451.30 [code]....
View 6 Replies
View Related
Jan 5, 2010
I have daily basis monthly sales. Now I want to summarize into monthly gross. Pls look attached file. I am looking for a formula to summarize January daily sales from date 1st to 31 st as of just January and and sum of each day gross.
View 4 Replies
View Related
Jan 4, 2005
I have created a chart on excel for us to track daily sales but also to figure sales tax so we know what to send the IRS each month. We have been figuring the sales tax ourselves and
filling in the chart on excel but I would like to create a formula that
automatically does it for me based on total sales.
View 9 Replies
View Related
Jul 13, 2014
What would be the best way to apply conditional formatting where you actually want to apply a background and foreground colour to cells that have many different item numbers, ex:
6201 = Dark blue font, light blue background
6202 = Dark Green font, light green background
6230 = Dark Purple font, light purple background
6245 = ...
6445 = ...
6447 = ...
6448 = ...
and the list goes on for about 200 different colour combinations.
I know I can do this in conditional formatting using the ribbon, but I wonder if there was a faster way to do this
View 6 Replies
View Related
Feb 3, 2014
I created a macro that will do a subtotal and copy the value then undo the subtotal and delete all the values(i dont need the actual raw data, just need the subtotal). i noticed when i do a copy, i have to paste from the office clipboard. is there a macro i can use to paste from the first item on the list?
View 1 Replies
View Related
Mar 3, 2008
I am working on a sales sheet for my business. I have a worksheet that has the names of everyone in my store that has sold anything in column A. I want to create a list that has just my full time sales people and will delete everyone else.
View 9 Replies
View Related
Mar 27, 2013
I cannot solve with Excel 2010 and I have searched all over for the answer.
I have sales data that is approximately daily and would like to count the monthly data and summarize it as an average for the month in a separate column.
For example, I would like to turn this:
3/2/2005 $xxxx
3/5/2005 $xxxx
3/20/2005 $xxxx
4/2/2005 $xxxx
4/10/2005 $xxxx
Into this:
March 2005- $xxxx (monthly average)
April 2005- $xxxx (monthly average)
I have a feeling some 'countif' formula would work but I am not sure how to do this.
View 4 Replies
View Related
Oct 3, 2012
In cells A4 to A54, their names are displayed.
In cells I4 to I54, their total sales are displayed.
Starting in cell B59 and C59 I'd like to display their name and sales respectively.
Also in column I I'd have a second value which would be a total of each department. Would it be possible to leave this data out of the formula? No big deal if I have to put that value in the next column.
View 9 Replies
View Related
Oct 14, 2009
I need to copy the values of a range on the weekly sales worksheet to the monthly sales worksheet. The last column is the total on the weekly sales. Part of the heading of the total column is the week ending date (e.g. 10/17/2009. On the Monthly Sales I have the months in columns by week ending (e.g. 10/17/2009).
Range I4:I28 to the monthly sales worksheet by date.
View 10 Replies
View Related
May 30, 2009
This is going to sound very simply to most here I'm sure. I have a need for a simple ledger, credit, debit, balance. I will only have maybe 12 accounts and want to keep track of hand written invoices and payments.
Here's what I would like: I return to my office, click on a tab for an account, enter the date and amount of an invoice in a debit column for that account. When payment is made, enter the amount received in the credit column. Having a running total in the balance column. A monthly statement option would be nice, but not necessary.
I have searched for templates of general ledgers and get results with budgets, projects . . . just too complex for my needs now.
View 11 Replies
View Related
Feb 8, 2012
way to delete multiple rows in a large spreadsheet with automation Example:
84076 Cantor 10 Retail Tue Nov 08, 2011 160 REVISED Division 8 1600 Compulsion Done 84077 Cantor 10 Retail Thu Nov 17, 2011 160 CANCELLED Division 8 1600 Compulsion Done 84078 Cantor 10 Retail Thu Oct 13, 2011 88 Division 8 880 Compulsion Done
I would need to delete all 4 rows within the "Cancelled" Box, but in a spreadsheet of 12000 boxes of 4. so, probably a macro to find them all, and delete them would be the way to go.
View 2 Replies
View Related
Nov 12, 2009
Ref attached sheet
I m facing a big and urgent problem in my worksheet..i have done my all work here but now i knew that there is a difference in this vb code..
I need your urgent favour to rectify it for me..
What i need is sheet1 is my journal jounal (data) and sheet2 is my reporting place where i set a macro button at top to update the report..
But in sheet2 column I i need an auto Balance of debit & credit..
View 11 Replies
View Related
Jul 31, 2006
Attached some sample data I am working on Data is ledger printed from SAP R/3 to .pdf using pdf995 Stripped from .pdf to .xls using Able2Extract
Column A represents unit number
Column B represents unit name
Column C I pulled account number from Col E if number otherwise 0
Col D I concatenated Col E,F,G is text color blue, otherwise 0.
I then filled Col D down to replace zero with value above non-zero.
Problem :
There is no account number in C2. I thought about using text in D2 for example and creating a lookup table. The problem with that approach is that exact samy text appears for balance sheet account as ledger account. Salaries& Wages for example is both accnt 216103 (bal sht) and accnt 911000 (ledger activity).
View 4 Replies
View Related
Nov 18, 2009
how can transefer data from journal to ledgers accounts,
i can do journal and its formulas, ledger as well,
but i dont know how to connect them to avoid entring data 2 times.
View 14 Replies
View Related