I am so frustrated to combine several spreadsheets into all in one, and seperate all in one spreadsheet into several with the same header frenquently. Pls check the attached example excel spreadsheet., could anyone teach me how to program in vba to achieve the result as above?
We have 13 salesman each completes a quotation log (each log is identical - except for the data obviously)
For month end and reporting purposes it would be useful to have 1 single log sorted by date.
It is not possible for each sales man to input the data into one log as some are away on business and take the log with them.
The logs are all stored in the same directory however there are other files and excel spreadsheets in the same directory.
This is how I would like to see it working - On opening the "MASTER" sheet it automatically (no user input required) clears out the old data, then gathers all the data from 13 spreadsheets, sorts it by quote date and dumps it into a new workbook.
We will be using excel 2000, 2003 & 2007 I can enable macros on all machines if necissary.
The spread sheets are very simple, single sheet with no formulas - just manually adding data. There will be no blank rows and data will be held in the range A - S with rows 1-4 being headers and titles
I have two very large spread sheets that are worked on individually. Because of there size, sharing them causes issues and lock-ups (excel not reasoning) and lengthy download time.
They are now worked on individually with two separate file names.
I am trying to find a way, even a program, to at the end of each period, combine the files and update the data that would have been input.
I have attached examples of what might take place.
Example 1
A B C D E F
[Code]...
Example 2
A B C D E F
[Code]...
As can be seen, Example 1 has had information added to Columns A, B and C, whereas Example two has had info added to Columns D,E and F. Also, the data in Example 1 (E,3 and F,3) has been updated to reflect No with F,3 changing to "Blank"
The result is to combine both spread sheets and update the added / amended data into one spread sheet that shows an updated position as follows.
I have a wookbook with serial numbers in column A and barcodes in column B. There are 51940 rows in total.
Is there a way I can search through column A, and for each unique value found , concatenate all of the barcodes that relate to that serial number in column C?
I would like to know how to combine multiple cell value from each column to create a unique value. for example column A will have values (a,a,b,c,d,e,e,f,i,j) and B will have (1,2,1,1,3,4,5,5,6,7) and C has ( xxx,yyy,zzz,xxx,yyy,zzz,xxx,xxx,yyy,zzz).
i need to create a list of unique possible combination of data into column D.
I have placed below example and how to create a similar code and what functionality does this.
I searched and searched and I can't find an easy way to do this without using Access which I am rubbish at. Is there an easy way to do the following in Excel. I have several thousand records by row each with a unique numerical identifier. The unique identifier is the "Household". Within the household there are sub "Accounts". The sub accounts are truely unique. All the "Accounts" are in the same column.
What I am trying to do is combine the records into one row per "Household" with the accounts listed in successive columns. The maximum number of accounts there may be is 7 but it can be as few as 1. Example:
If I have multiple entries with different but repeatable text values in one column - how do I count all unique ones ? Is there a function or does it have to be a pivot table of sth ?
I'm trying to think of a way to do this. I need a unique ID number per company, so I would want it to look something like this:
0001: Apple 0001: Apple 0002: Google 0003: Sony 0003: Sony 0003: Sony 0003: Sony 0004: Ford 0004: Ford 0004: Ford
.....and on and on for about 400+ rows. I think I know how to state the condition. IF B15 =/= B14 then....assign unique ID, else A15 = A14....or something to that effect.
Each product is represented by a serial number (column A). The can be sorted on column A from smallest to largest prior to calculating results if that helps.
The repair list contains 1 entry per spare part used, so the same serial number may occur several times.
Furthermore, a product may have been repaired on several instances - so the serial numbers can span several dates (column B).
The solution i am looking for should return the number of unique repair dates per serial number. That way i can see, how many times each product has been repaired. Results can be displayed in an individual column.
I have two spreadsheets, A & B. A can be thought of the master spreadsheet and has one record for every employee giving personal information, including a personel number. B has 0, 1, or 1+ records for every employee in A. Each of these records has a personel number. I want to copy the value of a specific field from B to A.
I want to do....is...a macro or something....to be like "Hey...were going to match up column information based on the first column, there exists both the same data in both sheets....as you can see in A3 and A2, now, I want to be able to add the information that is in book1, into the "DBL approved", so like...the 2nd row, I want columns B-G to be filled via book 2 because its saying,
"okay, A3 in DBL has the data 00-120-01, but there exists no data next columns over...but oh look, A2 in Book1 does, let's fill in that missing information!" so now it'll fill in the bullet points, brand, segment, etc."
I am working with excel2003. I have to different workbooks in which on is a Bill of lading that is used to show the items being shipped. The other workbook has a list of the items that we ship.
On the BOL I want to be able to type in the "L" number (or I.D. number) in D14, D19, D24, D29. By typing in the L-number I want it to automatically bring up the product name into cells E14, E19, E24 and E29. With the exact name of the item it will help prevent employee error. I want it to also bring over the exact weight over from the flavor list in cell range C5:C28.
I have the FlavorCodes range with all the L-numbers on the BOL in cell range U9:U48. I thought I had it set up correctly but when I select the L-number in the drop down list in cells E14, E19, E24 and E29 I get error code #NA. I thought I defined the cell range but it is still not working.
how do i access data from different spreadsheet tabs in a same excel file..e.g: if tab 1(student) and tab2(teacher), how do i access tab2 let say cell C4
I have two spreadsheets, The spreadsheet #1 has the information of two years and the Spreadsheet #2 is a montly report. The Spreadsheet # 1 hasta on the column A a number of transaction, and at the column Q the invoice number. The Spreadsheet # 2, only has in the column A the Invoice number. I need to find in the Spreadsheet # 1 the Invoice numbers that match the Invoices that I have in the Spreadsheet # 2 by bringin the "Number of Transaction".
For example: Spreadsheet 1: Has on column A3 the Number of Transaction # 0123, and at the column Q3 the Invoice number 555. At the Spreadsheet number # 2 I have the INvoice # 555 located on A10. I need to know what is the transaction # by adding a formula in a new column (G) so I want to have the Transaction # 0123 in the new column added G.
If I do this manually it will take me hours since these report and the Master is so big, and right now I am doin it using Ctrl-F
I have two spreadsheets (lets call them Spreadsheet A and Spreadsheet B). Spreadsheet A is a list of all my customers and Spreadsheet B is a list of potential leads which may or may not include some which are already customers. Is there a formula I could include in an extra column to highlight which ones are customers already so that I can ensure I don't call them.
If it makes a difference the two spreadsheets are stored on seperate locations on my computer.
I have been handed 503 spreadsheets containing system data for multiple business units in my company. Each spreadsheet is identical in structure (same column headings) but just varies in the number of rows of data
I’ve now been asked to add (4) additional columns to each of the 503 spreadsheets. The (4) new columns are to be called:
I have got a few spreadsheets which save information for each departments, about 8. They contain information on bookings & I am being asked to get information on certain date ranges, mainly monthly reports. (They are being filled in automatically from another excel file.)
Can I have a file which I could call "Master file" which has a macro in that looks up and copies all the relevant rows from each departments spreadsheet into the new master file. I dont think this would be too difficult (still beyond my capabilities though) My intial thoughts were something like (in half code half english).
Create a form where you specify your date range and a submit button e.g. txtstartdate txtenddate
Lookup 'G:FolderGeneral[General.xls] IF in between txtstartdate and txtenddate then paste
ActiveWorkbook.Sheets("master sheet").Activate Range("B2").Select Do
I have a spreadsheet which needs to be shared by 10 people. The spreadsheet is fairly simple but I don't know the best way to do this. I have heard of public folders or to share the .xls.
Im using this code. My data is in column A from Cell A2-A65000, and the data I want to compare against is in a different sheet col K cells k2-k65000. I need it to compare column A on "Sales Reps" against Col K "Data Sheets" and count how many times whats in cell A2-A65000 occurs on in Cells k2-k65000. So if 689 was in cell A2 and was listed in cell k3, k4, k5, it should return the value 3 in Cell B2. My formula is not working. Getting an error.
For Each ce In Range("a2:a" & Cells(Rows.Count, 1).End(xlUp).Row) ce.Offset(0, 1) = WorksheetFunction.CountIf(Sheets("Data Sheet")(Range("k:k"), ce.Value)) Next ce
I have three different spreadsheet, Spreadsheet A with the name of all the clients of the company. Spreadsheet B with the name of all the clients in Toronto and Spreadsheet C with the name of all the clients in Montreal. Now i have to create a fourth spreadsheet to lookup the clients who are not in the Spreadsheet A (basically trying to find new clients). How do i search for the new clients in all the three spreadsheet?
I have sucessfully created a spreadsheet which links to another using a vlookup and an array. I now need to reverse the process. My requirements are this:
I have individual spreadsheets which represent individual projects/quotes for our customers. I'd like each of our sales staff to have a summary spreadsheet which summarises all their ongoing projects in one spreadsheet, dynamically.
when a new project is created on a new (projects/quote) spreadsheet, it looks to the summary spreadsheet and looks to see if that project exists in the summary, if not, it creates a new line and writes the summary details to it. then as the project spreadsheet evolves, it updates its details on the summary sheet dynamically.
Basically I order thrice a week from a certain supplier to fill the shelves of my shop.
They make available to me two spreadsheet dumps: an orders spreadsheet, with the following columns:-
order ref date unique product code description qty
9262 03/07/2013 622642 Fabric Dye 4
And an invoices spreadsheet with the following columns:-
invoice ref date unique product code description qty
122551 04/07/2013 622642 Fabric Dye 4
Basically I can see the potential here to find out what we've ordered but that which they haven't sent which would be so handy.
I have tried to vlookup() produce code against product code, this kind of works but below are some example drawbacks:-
1) no idea if I've ordered it ten times and they've only not sent it once, or if I've ordered it ten times and they've not sent it nine times. 2) no idea of dates - i.e. ordered this in June '13, October '13 and February '14 and they sent it in February '14 only.