I have a sheet (Data) with thousands of records (transactions) of a number of different suppliers, first I have to select all the records of a specific supplier, here I'd like to use an inputbox, and copy them to another sheet.
View 9 RepliesBasically what I want to do is take any value that isnt "0" in column F and place it in another column in a nicely formatted table. So basically get rid of all the zeros...
Im sure this has been done and asked before but I have no idea what to search for or if there is some easy forumla in excel for situations like this.
I know this is probably very easy, but it sure has me puzzled: I have an Index & Match formula (G4) to extract the first set of data from the Sales Info, and then link (+) the Table's month & year to the drop downs, but I need the next 11 rows to automatically fill in for a 12 month summary.
View 8 Replies View Related1. Is there a way to delete all records below a variable row which I select highlighted ?
2. I have a spread sheet which is located in the folder xl start. How can i find this file to delete it>
I have a spreadsheet that contains multiple rows per user. I need a combo or list box that pops up asking them to select their name from the drop down list. The list would be based on column A and would only include their name once.
When they select their name, the macro would then open the Form option from the Data menu, the Criteria button would be pushed and their name entered into the form, to return the first record their name appears on, then they could forward through the records, and update using the Next record button.
I have a ListObject table called Table1, and the column name is Table1[Company]. The records have been sorted A-Z for the Company.
The records look something like this
Date Employee Company
-------------------------------------------
1/1/11 Jane A Company
1/1/11 Jane B Company
3/1/11 Bob B Company
2/1/11 Bob B Company
4/1/11 Bob B Company
5/1/11 Bob C Company
What I need is a sub() to look in the column of Table1[Company] and determine:
The # of records that match the criteria given the Company name, and where they start and end. Ultimately, I'd like to copy and paste this portion of the table to another spreadsheet.
So for instance, if the Company = "B Company", then I could find out there are 4 records and there locations to copy and paste them.
I have a spreadsheet which is used by users unfamiliar with Excel. They are using the filter to select records, however when this is used some records appear which have no entry in the cells of that column. Can I overcome this? There is no data in the blank cells, other than a data validation drop down.
View 1 Replies View RelatedI'm trying to add a formula to a sheet comparing multiple suppliers rates for different items, in a table similar to this:
Item Supplier 1 Supplier 2 Supplier 3
Socks $3 $4 $5
Shoes $10 $16 $12
Pants $17 $11 $12
Shirts $79 $25 $20
I've added the usual max, min, average formulas to it, however what I want to do is create a column which will tell me which supplier has the lowest rate, therefore for it would show as a column showing Supplier 1 is cheapest for socks, Supplier 1 cheapest for shoes, Supplier 2 for pants and Supplier 3 for shirts.
So for example I Have three suppliers for a given delivery depending on weight to point "A" to "B"
From To AA pallet AA semi AA fullload BB pallet BB semi BB fullload CC pallet CC semi CC fullload
A B 1 2 3 4 3 4 2 3 4
A C 2 3 4 3 4 5 3 3 4
Given weight parameters that
min max mode
0 < 4 pallet
4 =< 6 semi
6 =< 12 fullload
Now I need a a table like this
Weight From to Mode Cheapest supplier pri
abcxyzzyz
1009080zyz
506070abc
Have a formula that quotes the supplier at cell D2 , is there a macro that will highlight the minimum value each line that is 80 and 50 to be highlighted
Yes I know =min(a2:d2) will do the work
I have a supplier list which i access all the time. To view individual supplier details i use find function using supplier code and then scroll through the sheet to view all the details and it is quite hectic. Is there any way i can modify the sheet so that every time i search for a supplier , only that supplier details can be shown in a different sheet or notification which i can also print if i want.
View 2 Replies View RelatedColumn O is a list of unique 1000 supplier codes
Column G is a list of 75000 imports (dollar amounts), each row has a supplier code.
I want to have a formula in Column P to sum the rows where the suppier code equals the supplier in column O.
i thought this was easier in 2007.
I have a sheet with the pricelist and a sheet with the actual price offer. I would like to copy selected Items from the price list to the price offer. I dont want to do that by copy and paste method. I would like to create check boxes, so that I can check / uncheck each Item I want to be copied and then press the button copy to the price offer ... how can I do that. I asssume I would need a macro for that. The columns in the pricelist and the price offer sheet will be identical of course.
View 8 Replies View RelatedI have a new request concerning my warehouse managing.
I am getting back to this thread [URL]....
The request is as follows: When having the list of all the shelves, I'd need to create a new sheets with following data in a single column
shelfcodequantity
reporting in order all the shelves with code and quantity.
My idea is as follows:
1 - to create a sheet with all the shelves
2 - to create a function that reads in this sheet the name of the first shelf, then searches in other sheets the shelf and copies to a result sheet all the records found
3 - then passes to the following shelf name
4 - and so on, until the shelves lists ends.
Please note that in each sheet I have these data in two columns.
I am here attaching you an example
My problem is this:
How to tell in VB to realize step 2 ?
I am trying to copy all the records with condition TRUE as a cell value. I have more than 3000 records and 20 columns in two files. We have same data in both the files. My scenario is, I need to find the missing records in file 1 from file 2 and append those missing records to file 1. Similar work should be done on file 2 also. I have been using =ISNA function that returns "TRUE" for missing records. I need to copy those "TRUE" records to file1 from file 2 and also to file 2 from file 1.
View 6 Replies View RelatedI have a column for suppliers names and a total column The suppliers names differ. How can I create a separate column to show the total of spent for each supplier. I'm not up on excel code
View 4 Replies View RelatedFollowing problem:
I have a workbook containing data about some students grade on different sheets (4). The structure is almost the same on every sheet:
The first 2 row contain headers the each row contains a name and result for different test:
name - test A - test B - ...etc.
Peter - 90 - 60 -....etc.
there are 25 columns on each sheet - 4 sheets in all.
What I want is, to consolidate each students results in a separate workbook.
I need to create a new sheet, and pull all the rows (14 columns each) from another sheet in the document that meet a certain criteria. Fortunately, that criteria is all in one column; unfortunately, out of about 30 different entries in that column, I need it to pull 7 of them, 4 of them have been input 2 or 3 different ways.
I know I can go through the list of 900+ by hand and copy the rows, but there has to be an easier way right? Here is the list of all the spellings of the ones I need to pull..
D.C.
DC
DPM
DPT
Ed.D
EdD
EDD
JD
Ph.D
Pharm. D
PharmD
PhD
I have created a loop to go through a series of records on one spreadsheet, and if it finds a "100.00%" case in column G then it will cut that record, move to another spreadsheet and paste it there. I am so close but I can't seem to get the pasting part to work and I've tried quite a different number of things.
Dim cellPercentage As Variant
Dim cellLocation As Range
Dim x As Integer
Dim found100PercentCases As Range
Dim cellAddress As String
I have two tables "Table A" and "Table B" with records in rows (typically). On one Worksheet I have a report with 10 empty "slots" ("Slots A") waiting records from Table A, and 10 empty slots ("Slots B") waiting Records from Table B. In those slots, fields are arranged one below another.
PROBLEM 1. I need to find a way how to automatically copy chosen records from table A to slots A, and from table B to slots B. There is no rule how many records, and which records are to be copied. Some slots can stay empty.
PROBLEM 2. Slot B has the same number of fields as the Table B, plus one more field, let's call it "extra cell". In the "Extra cell" goes a result of a function, which arguments come form that Slot B, but from ONE Slot A as well. So, I need to "link" every Slot A with one or more "Slot B"s.
I have an auto generated report that is exported into .csv, my dilemma is that it sorts both the account code and the date in the same column, like this:
9884A B Gerrish Ltd œ 577.270577.270
30/09/201231/10/20121865518655277.010277.01MAL/553260
30/09/201231/10/20121884218842264.260264.26MAL/553259
30/11/201231/12/2012195541955436036MEL/549200
so "9884" and "30/09/2012" are in the same column.
Some how i need to turn this information to say the below three invoices are for account 9884, into more of a usuable data like:
9884 A B Gerrish Ltd 30/09/2012 etc etc.
Obviously through out the document this repeats for each account, upto some 1000 accounts.
jas.xlsx
If you navigate on the file menu in the excel window to: Data>Filter>Advanced Filter
Then select:
Copy to New location, Unique records only. You can arrive at the macro
I have a range of columns i.e. 23 columns (i.e. B through X). Someone can write records in these columns (starting from B21).
Duplicates are considered the rows with similar data in columns 3 and 11. I know about the removeduplicate method and works really well but i want the duplicates not to be removed. Instead another column shall be checked for date of entry (user will entry date in format dd/mm/yyyy). The newest entry will change the value of the cell in column 4 (islatest column)to TRUE while all other records will be FALSE. This will work with the filtering of data on a pivot table on another worksheet.
Book1 and Book2 are workbooks that I have modified in order to protect private information.
Book1 will have 11,000 records (my example Book1 has only 100). I need to rearrange Book1 such that it looks like Book2. Book2 has 20 complete records from Book1 combined into one single row, and my example Book2 has populated 3 rows only (3 rows x 20 records, making 60 records now appear on 3 rows only).
Macro for getting Book1 to Book2? 11,000 records in Book1 will take a lot of hours to transform into Book2 unless a macro can do the job for me.
Book1.xls
Book2.xls
I have a base document that i can import another data document with a button (this is working).
I then have another WS ("search") in the base document with lots of identifiers which I want to use as my search range to look through the document that I just imported (column A).
I need a msg to ask the user what month they would like to find the $ value on (Ie, January) in the imported WS... this way it doesnt copy the entire line only cell in the selected month column.
Then I want to the user to be able to click a button that will check through the identifiers on the "search" WS and if the same identifier appears in the imported WS in column A, then for the $ value in the column selected to be copied to the search Range work sheet.
If the idenfifier is not matched then in place of the $ value copied can be the string "no in XXX WS" .
I have attached the document with dummy data in each work sheet with details more cleary what I have meantion above.
example of a database user form that will allow me to list records in a sheet as well as search for records in a sheet. I know excel has a built in feature for this but it is menu driven and I need something that is button driven and will allow me to resize the form layout. I was not able to figure out how to do that with the built in form.
View 7 Replies View RelatedThis is a sample of the data with which I am working. I know that a macro can do what I need, but I am only versed in Excel formulas and not that much programming. I need to be able to first sort the data by Column C ("Element Type"), then by Columns E, F, G ("Year", "Month", "Day"). Then, I need to be able to copy all rows that have the same "Element Type" and "Year" to a new file, using the same header from the original spreadsheet on each new spreadsheet - doing this multiple times until the end of the file is reached.
Ideally, the new files would have a strict naming convention: XXXXXX-ZZZZ (YYYY).xls, where the X's are the value of the "COOP Station ID" in Column A, the Z's are the "Element Type" from Column C, and the Y's are the "Year" from Column E. If this theoretical macro were run with the Sample Data file I provided, it should result in the creation of five new workbooks. Is there a way to write a macro to do this, or at least something similar
I have a list of invoice #'s on a sheet named "Temp Sheet".
I have a VBA macro that created a new tab for each entry and named it the invoice #. So basically the vba code created a new tab ( based on the number of invoice #'s on my list ), and named each tab an invoice number. So if I had a list of 10 invoice #'s, named S1-S10, the vba code created 10 tabs, named S-1, S-2,. S-3.....
Now to my question.
I have a template sheet I want to copy from ( "Template" ), and select any sheet that starts with "S", and copy/paste this template to.
I need code that will look for a number in cell C1 and then it starts at C9
selecting cells to copy until it sees a cell with a number larger than what is in C1 and stops.
i need code for this since the number in C1 is always changing
Example: if there is an 18 in C1 it selects all cells that are >0 and <19
these cells are always sorted 123456........36 they are not random in order like 1 17 6 12 18 4 3
I have a macro to search column E for an occurance of the text ALPHA,(there is more than one occurance of the text, so I'm using a loop to search for the next occurance,once the first one is found. No problems there).
Once I find a Row that contains ALPHA( say Row 15), I want to Copy the columns A15:R15 (NOT the EntireRow).
how I can Copy only a Range of Cells and not the EntireRow?