Extracting Data From Inventory Report
May 11, 2009
I have made several stabs at extracting data from an inventory report, but keep hitting roadblocks and would appreciate some help in my 1st VBA project.
I have attached a spreadsheet with sample data - the data is in a fixed format, and I have overwritten unnecessary data, but all the data to be extracted is in Columns D, O, and P.
My approach was to go down column D testing for non-blank fields that do not begin with "Total", thus creating an array of item names. Then I would add the code that would go across adding Quantity and Value into the array.
View 7 Replies
ADVERTISEMENT
Apr 24, 2009
I would appreciate some pointers on how to write a function(s) or macro to do the following in Office 2003:
- I get a weekly report (from Quickbooks) in Excel format which shows inventory usage.
Showing the relevant sections:
Col D Name
Col O Qty
Col Q PxQ
The report also throws out Total is Col D, but only for Col Q, not Col O (which is needed)
So, R5 thru 7 may show Apples
And R 11 shows Total Apples
Then R13-14 may show Oranges
And R15 shows Total Oranges.
The report has about 400 lines currently, and about 50 items......
View 8 Replies
View Related
Jul 2, 2008
How do you create a macro to copy the information from my weekly reports to a monthly report and be able to update automatically. If you had 4 worksheets (for each week of the month) and 1 mastersheet for the whole month in a workbook. All titles are the same and If you needed to copy all the data that is in the columns, say, A through I, starting with row 4 to however many rows are in a given week. The reports can be made up of numicerial values, text and dates. Let me know if more information is needed or an example worksheet.
View 4 Replies
View Related
Sep 10, 2012
How do I print multiple inventory labels based on our inventory levels?
For example:
Item No 1000
Descr Window
Customer Taylor
PO 9001
Quantity 10
Item No 1010
Descr Door
Customer Jones
PO 9011
Quantity 35
I want to print 10 labels with the info from Item No 1000 and 35 labels from Item No 1010????
View 8 Replies
View Related
Mar 20, 2014
I have a directory folder with an active workbook and another workbook id like to copy data from. The Following macro opens up a file in the same directory, copies some data and pastes it in the active workbook. However with this code I have to specify the filename, 'Data.xlsm' in the example code. I would like it to copy data from the only other workbook in the current directory WITHOUT having to specify the name in the code, so just opening it up no matter what filename it has.
In addition I would like to extract the filename from the workbook im copying data from and paste it into the activewoorkbook in sheet 1 Cell A1. I had a look at getopenfilename function but cant seem to make it work for my purpose.
View 4 Replies
View Related
Aug 18, 2009
I had been trying really hard to get some solution on COGS valuation & inventory valuation on FIFO basis. my daily transactions are typical sales & purchases.
Attached is the inventory in/out movement from Quick Books. in the column "Num" type bill is the entry from purchase bill whihc always has a reference as P/O####. This is how i will capture the landed cost against a PO., another type "Inv Adj" is inter warehouse transfer. Name is cusotmer, Inventory is my item number. in some cases it is like "2000", in some cases it is 10000:10121, and in some cases it is 10000:10200:10201 that is why they fall in different columns when i export them.
What I would lilke to do: 1) Run a report by month, by customer showing cost of goods sold on FIFO basis, I can capture sales amount by running another report.
View 11 Replies
View Related
Aug 8, 2006
I am not an excel whiz, so I will need layman's terms if possible. I may have found a solution to my problem under another thread, but I couldn't understand it. I have only briefly worked with macros, a very long time ago. Here goes...
I have an inventory list that has not been updated for sales of the inventory, only for purchases. I have sales lists, generally by month, in separate spreadsheets. I am looking for a simple, efficient way of either eliminating or at least matching up the data in the sales list to the inventory list in order to remove sold items from inventory (on paper). I am hoping the result will be a fairly accurate inventory list, and related value, so that a physical inventory count won't be necessary at this time. Obviously I could copy all of the sales info into one spreadsheet, sort by inventory number, then manually delete all sold items from the inventory listing. I am REALLY hoping there is a better option.
View 6 Replies
View Related
Feb 27, 2007
I have a workbook. The second tab is inventory numbers and the first tab has my inventory items. I scan in the inventory number (unique) and it adds it to my inventory sheet. As I use inventory I scan the barcode and it inputs the serial number into my used inventory tab. What I need to do it when I scan the serial number for used inventory into the used inventory sheet to have it go to the inventory sheet and delete the line for that serial number.
View 9 Replies
View Related
Aug 30, 2013
I have two worksheets in my report cards:
1) Data - the students are listed in Column A, math scores in Column B, reading scores in Column C and science scores in Column D. The grades of 300 students are entered in this sheet.
Student
Math
Reading
Science
Jimmy
75
84
100
[code].....
2) Report Card template - This is the report card that needs to be generated for each student. It's pulling the student name and grades from the Data worksheet.
Student Name
=Data!$A2
Math
=Data!$B2
Reading
=Data!$C2
Science
=Data!$D2
How do I create worksheets (report cards) for additional students? I have 300 students in the school. I need the next worksheet to reference Data!$A3. I know how to cut and paste the report card template and then edit =Data!A2 to be =Data!A3 to create a report card for Sally. How do I create the 300 report cards I need?
I've been teaching for 13 years. I can create a report card for each student in my class and edit each worksheet individually for each student. Now I've been asked to do this for the whole school and I don't know how to create the other 299 sheets I need.
View 1 Replies
View Related
Jul 4, 2014
I added an attachment with an example. You will see a matrix, with in the most left column an ID, then the headers of the matrix has different numbers that mean something.
What I want to do is, you fill in a number in the combobox, then press on a button and a report will be made on a new worksheet. With a list of the numbers and dates in the matrix that the ID has. Like shown in the example sheet in the workbook.
View 1 Replies
View Related
May 31, 2006
breeze.
Here is my situation:
X Y Z
Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep
A
B
C
The above format is how I want my spreadsheet to look like. On another sheet within the workbook, I have a chart with some control options that allow me to vary X, Y, Z (I can vary either of these individually). Anytime I vary any one of X,Y,or Z, then the values in A,B,and C all change. This is what I am looking for, a Macro that will allow me the opportunity to everytime I change or vary an option (X,Y,Z), I can hit "Save Scenario" and the new values will be appended to my "Report" sheet. The values of A-C and X and Y are kept on a sheet entitled "FY 07". X is maintained on a sheet called "Inputs". So in all, X,Y, Z and A,B,C are all copied for each scenario.
View 9 Replies
View Related
May 20, 2008
10/05/0808:30:00 AMValid Card EntryLamDoor 1
10/05/0808:31:00 AMDoor Leave Open Door 1
10/05/0808:32:00 AMDoor Closed Door 1
10/05/0808:41:00 AMValid Card Exit Yap Door 1
10/05/0808:46:00 AMValid Card Exit Lam Door 2
10/05/0809:14:00 AMValid Card EntryLam Door 2
10/05/0810:18:00 AMValid Card Exit Lam Door 2
10/05/0810:19:00 AMValid Card Entry Yap Door 1
10/05/0810:40:00 AMValid Card Exit Yap Door 1
10/05/0810:42:00 AMValid Card EntryLamDoor 2
11/05/0808:30:00 AMValid Card EntryLamDoor 2
11/05/0808:20:00 AMValid Card EntryYapDoor 1
11/05/0810:20:00 AMValid Card Exit Lam Door 1
11/05/0811:40:00 AMValid Card Exit Yap Door 1
the above is the data i need to work with (dates are in dd/mm/yy). i need to find a way (a macro, preferably) to extract the date and time corresponding to one person, and paste it into a new sheet, e.g. at sheet 2 (renamed to "lam") should be
10/05/0808:30:00 AM 11/05/0808:30:00 AM
10/05/0808:46:00 AM 11/05/0810:20:00 AM
10/05/0809:14:00 AM
10/05/0810:18:00 AM
10/05/0810:42:00 AM
View 10 Replies
View Related
Jan 23, 2013
I have a spreadsheet full of data and I need to extract only those lines of data which can be identified by the last three letters in a cell. I am adding an example spreadsheet which highlights the rows I am trying to extract based on the contents in column B but only where column B ends in KY.
Example data extract requirement.xlsx
View 5 Replies
View Related
Jan 4, 2010
I have attached a spreadsheet that has the following tabs: powerhouse report, influent clarificatioin report, and data history. The PH report and Influent report are supposed to take data from the data history tab. which is named POWER. My problem is that the PH report does take data from the data history tab but the Influent report doesn't.
View 2 Replies
View Related
Feb 7, 2008
I have a workbook of about 30 worksheets which makes extensive use of Data Validation. Essentially, data entry cells is validated against values within some predefined ranges (there are 6 ranges in total).
I am preparing documentation for this workbook and would like to document the data validation being used. Is there a way to extract this information? Where/How is it stored?
View 9 Replies
View Related
Jul 16, 2008
I would like to transfer data from one tab to another to for a report.
First off I would like to take all the rows that have "O" in column B. But only if the year in coloumn Q or R is this year. (Q is order data so I show all new orders taken this year) (R is despatch date so I show all orders despatched this year)
There will be around 25 orders total
I would then like to add a total to the bottom of this set of data.
Then move 4 rows down and bring all the data that has a status "HP" in column B regardless of any other criteria.
There will be upto about 100 Hot prospects (HP's)
My data is held on tab "BRAND" and I would like to generate the report on "BRAND HP"
My data has a header row in row 1 and holds data from A:T
The data is of ever increasing length.
View 9 Replies
View Related
Aug 24, 2007
I want to create a report from an existing excel spreadsheet that will show the first row with column headings. I only want certain columns from the spreadsheet to show on the report (see attached). On the left (1st column), I want to show the data from each of the last 7 days. Then I want to show data for rolling last year to date, then for last rolling year to date, and finally for last rolling 30 days.
View 2 Replies
View Related
Jan 18, 2008
I have a large list of items (up to 3000) that are listed by category, description, part number and weight in columns C-F. Column A is where a user will enter a quantity in the rows of the items that they want. Once the user is done picking their items they hit a button that I have written a macro in, to copy and paste only the rows that contain a quantity in, on to another sheet. My problem is that for these items (that contain a quantity) I need to have a line number (in column B) that starts at 1 and counts up for each line item. Issues to consider:
-Line item 1 may not always be the top item. (because the user may not choose the top item)
-There will be spaces inbetween the line items since the user may not pick all of their chosen items in order.
-Users can pick an item at the bottom then pick one above it. (The Line item numbers will need to readjust to include the new number in the middle)
This has been bugging for a few days now cause whenever I think I finally get it I keep creating circular references
View 2 Replies
View Related
Apr 12, 2009
find the attached Example file. I need to create a report either by using or without using VBA.
View 3 Replies
View Related
Jan 5, 2009
I have two exported lists of data to compare from my newsletter program. One is the list of email addresses that I sent the newsletter to and the other is a list of the bounced email addresses.
Question: How can I extract the bounced emails from the list of sent email addresses?
In other words I want a list of clean, sendable email addresses.
View 10 Replies
View Related
Jul 12, 2013
I have created a excel document. Would like to create report using the data.
create reports using excel data
View 3 Replies
View Related
Dec 13, 2013
I need to extract and format a large data base of county real estate tax parcels for a report. See attached. How can I combine these or is it possible.
View 2 Replies
View Related
Aug 8, 2012
trying to do anything other than the basics with Excel. I would like creating a macro to import data tables contained in a report that I receive in HTML format.
The Report looks like the following. I de-identified the data
VRAAPCO1 VER 8.1 VANMANDY ADMINISTRATOR
PAGE 1 01201010 CONNECT SUMMARY CPU 046E16
REPORT DATED: JUN 12, 2012 17:53
INFORMATION AS OF: JUN 11, 2012 23:09
EXTRACT FILE(S) USED:
DDName=SVSAM DSN=SYSL.VRA.SVSAM.EXTRACT
DDName=MVSAM DSN=SYSL.VRA.MVSAM.EXTRACT
MASKING CRITERIA: MGROUP=FSCSC OR MGROUP=FSCORE OR MGROUP=FSTECH OR MGROUP=FSBSM OR MGROUP=FSCCS OR MGROUP=FSEMFE OR
[Code] ........
The column group represents a security profile for an application. I am auditing the access to that application. They either start with the '#' symbol or the '@' symbol.
I receive this report once a quarter. This being my first time. I had to manually cut and paste the data tables from the HTML report file into a spreadsheet.
This is what I would like to be able to do in excel using VBA. The report name is the same every time. 'FS.ASET.Users.htm'
1. Would like to create excel spreadsheet with a macro button and master worksheet that already has the headers.
Group Userid User Name Owner Create Date Special Operations
Auditor Revoked ADSP GRPACC UACC Authority
2. That looks in a network directory(will be the same everytime) for this htm file(will be same name every time)
3. Pull only rows that start with # or @. Place them into single worksheet with sorted by 'Group' ascending.
4. A stretch goal would be to have the macro create a different worksheet within the spreadsheet for each profile name, I.e.,@UFSGUAR, #ZCBSAU, and copy only those rows that start with that profile name into each subsequent worksheet.
View 5 Replies
View Related
Mar 28, 2007
First I copy and paste the text into Excel. All of the data is in column A because of varying lengths, which seems to work fine. I've already written VBA code (shown at the end of this posting) that reads the contents of each cell in column A, pulls out the key information (like the actual error code or invoice #), stores the fields in variables, switches to a separate tab that has a more user-friendly layout, locates the next empty cell in the appropriate column for the field, and puts the variable contents into the cell. I realize that the code currently has some variables that aren't being used and other basic issues, but that's not really what I'm after at this point.
The report itself includes billing errors, with each billing error separated by a series of asterisks. My current code works just fine for sections of the report like the first section in the sample below, where the billing error only contains a single invoice, error code, description, and action item. If the entire report was formatted in that manner, my task would be finished because it does those sections perfectly well.
But...if you notice in the sample report below, the third billing error contains more than one invoice, error code, description, and action item. Obviously, using my current code, when the macro encounters that section, it continues to function, but the rows do not align properly in the separate worksheet because there are extra invoices, error codes, descriptions, and action items. I just cannot figure out a way to have the macro keep the information aligned. In other words, in the third section of the sample report below, the billing error contains more than one invoice and item.
One approach that I've thought of is to try to count the number of rows between the cell containing the previous set of asterisks and the cell containing the next set of asterisks, because it's the information between each set of asterisks that I need to work with. But I haven't found a way to do that and don't even know if that's the direction I need to go or not. I'm pretty much lost at this point.
Here's a sample of the text report as it appears in column A (the fields are actually longer than this - I tried to simplify as much as I could for this posting): ....
View 9 Replies
View Related
Aug 28, 2006
It does everything I need it to do but I would like it to sort 3 columns and then merge the data. I have included a sample with a "WishReport" tab the shows what I am looking for. If possible (not imperative) I would like it to add a "unit cost" column and provide a grand total.
Option Explicit
Public Const SZ_INPUT As String = "SheetData"
Public Const SZ_OUTPUT As String = "Report"
Public Const I_FIRST As Integer = 0
Public Const I_LAST As Integer = 1
Public Const I_DATA As Integer = 2
Public Const ROW_HEADER As Long = 1
Public Const ROW_DATA As Long = 2
Public Const COL_FIRST As Integer = 1
Public Const COL_PROJECT As Integer = 2
Public Const COL_LAST As Integer = 5
Sub CombineProjectData()
Dim lInputRows(I_FIRST To I_DATA) As Long
Dim lFirstInstance As Long, lOutputRow As Long
Dim szData As String
Dim iCurrColumn As Integer
Call FindRowArray(lInputRows(), , COL_PROJECT)...............
View 5 Replies
View Related
Dec 4, 2006
im creating a spreadsheet at work that gathers project and FTE figures.
I have attached a copy, but ive had to totally simplyfy it to get it to the required uploadable size.
Basically each team member goes into the sheet tab with their name on it (the full version has about 40 sheets), enters the total hours per week they will be spending on each project.
Then the manager opens the spreadsheet, clicks on get data then enters the date. Data for the selected date is then shown. However I need excel to go into each persons sheet, take the names of the projects that will be worked on (obviously the ones that are not blank) then report the names onto the front sheet under "name of projects".
can anyone help me with this? as you will see, im self taught with vba etc so its probably not the best but im trying.
View 9 Replies
View Related
Aug 21, 2007
I'm trying to make an excel spreadsheet for a Kareoke business - what I want to do is have a database of CDs that they have in 1 sheet - this will include Song Name, Artist, CD Title, Track Number and CD Number - then I want the user to be able to go into the next sheet and type in the CD Number into a specified cell and thus will import all the information for that CD number into the required fields. I have attached a copy of what I sort of want it to look like. I don't really know the functions of Excel, but I'm quite computer savvy and should be able to work it out with instruction - please note, I've never used a "macro" and don't know what they are - so if I need to use a "macro" would you be able to give me a link to somewhere that explains what they are...and how to use them?
View 3 Replies
View Related
Nov 18, 2013
My query is , I require the UCF column for the ID having maximum number (last five digits) of UCF column for the respective year column values
IDUCF Yr
559589876543212610007971212
559589876543212610000333712
559589876543213610004703013
563829876543212630000323912
My answer should be like below (or) having an additional column saying Active/Inactive.
IDUCF Yr
559589876543212610007971212
559589876543213610004703013
563829876543213630004703113
563829876543212630002782312
View 1 Replies
View Related
Jul 30, 2013
I am trying to extract data from a website:
[URL] .....
I looked at the source code of the website and realized that if you notice (above) that the variables listed in the link (i.e year, month, day) are exactly what i need to change in order to get the data for a specific date. how can I accomplish this using VBA. so say I have in on an excel sheet year in column A, month in column B, and days in column C (time interval is constant so we don't have to worry about stime and etime). and i run the macro and it loops through each row taking year,month,day for all rows and saving the data as .csv or xls files?
View 1 Replies
View Related
Feb 19, 2014
how I can extract data between two strings?
Example: My string is: -_creativename-_spongecell-_creativesize-_300x250-_creativetype-_spongecell-_
How can I create a column that contains everything AFTER 'creativename-_' and before '-_creativesize-_'?
View 11 Replies
View Related
