Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Split Data Into Separate Spreadsheets

every month I import (from SAP) Local Authority staffing data for 200 schools into an Excel template. After a bit of tweaking I end up with a single spreadsheet showing formatted and adjusted data for all schools, with relevant headings separated by page breaks.

This is fine if for distributing a hard copy to schools, but I want to email it. I've seen macros that would allow data to be split onto separate tabs, but is there a way to break it down into 200 separate spreadsheets? I confess to not knowing any VBA, so be gentle with me

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Separate/Split Data In One Cell To X Cells
How do I split data in one cell into three cells?






Not all the data is the same, some have more words than others.

View Replies!   View Related
Creating Separate Reports In Separate Spreadsheets
I have a giant data sheet. I have to cherry pick certain data out of the list and send it to certain associates, in a new spreadsheet.

I want to do this automatically.

I can create the macro to select the data I want, and copy it over (values only), but I cannot find out how to do it in a new sheet with a new name that automatically gets generated with a unique filename (probably using the date).

Essentially, I want to copy my data spreadsheet (post-filter) into a new spreadsheet titled X mm-dd (where X is whatever I pick). I then want to delete several rows from it.

Ideally, the macro would do this (I will put a * next to things I can handle the code for, to save work):Filter Data *
Copy Data *
Create a New Spreadsheet named (Person's Name) mm-dd.xls
Paste Data (values, formats, column widths) to the new spreadsheet *
Delete Several Columns from the new spreadsheet *
Rename the sheet it's been pasted to
Re-filter data (new filter) *
Copy Data *
Paste Data (values, formats, column widths) to the same spreadsheet, only Sheet 2 *
Set the header of the spreadsheet with (Person's Name) and the date
Save the new spreadsheet

There will be as few as 1 sheet (in which case I would only want one worksheet in the new excel file), or as many as 4 sheets. I don't need a fancy way to do this, as it will only be done once a week, and I can just make the various cases required to do it even if it isn't pretty, but I can't figure out how to manipulate other files that haven't been created yet...

View Replies!   View Related
Identifying Matching Rows Between 2 Separate Spreadsheets
I have 2 separate spreadsheets. Spreadsheet #2 is a full list of items. The item names are in Column A. In Column B is an associated primary number. In Column C is an associated secondary number. Spreadsheet #1 is a partial list of items. The item names are in Column A. The primary number is in Column B. There is no secondary number given in Spreadsheet #1.

My objective is to find out what items in Spreadsheet #1 have a secondary number in Spreadsheet #2, and to group them all together so they can be easily identified. I am attaching a couple of example spreadsheets to give an idea of what I'm working with. The example's are small so it would be easy to just eyeball them and see what ones match up however the real spreadsheets I am working from are thousands of lines long so obviously eyeballing them is not the ideal way of doing it. Is there a formula I can use to reach my objective?

View Replies!   View Related
Macro To Split Workbook Into Separate Workbooks
I have a large workbook with many worksheets which are all grouped into pairs - i.e. Sheet1 & Sheet2 go together, Sheet3 & Sheet4 go together; Sheet5 & Sheet6 go together, etc. etc.

I need a macro to divide this workbook into separate workbooks where each group of worksheets has a separate file of its own and I want to name the new workbooks after the second sheet in each group:

i.e. A new workbook for Sheet1 & Sheet2 called Sheet2.xls; a new workbook for Sheet3 & Sheet4 called Sheet4.xls; a new workbook for Sheet5 & Sheet6 called Sheet6.xls; etc. etc. etc.

View Replies!   View Related
Split Address Field Into Separate Columns
I have a spreadsheet with a few thousand rows, yeah i Each one of the rows has contact details for individuals. I have the address for each person in one field with up to four different sections, seperated by tabs. How do I go about seperating each part of the address so it is in a different column? I have tried text to columns, and it only seperates the first portion of the address, seperated by the delimiter 'tab'.

View Replies!   View Related
Split Large Range Into Separate Worksheets
The following code takes a large range of data (currently 20K+ rows) and breaks it out into separate worksheets. This takes a while, and I have been trying to insert a progress bar into this macro, but the progress bar goes in reverse (from 100 to 0) but the userform will not unload at the end.

Sub MoveCells()
Dim objBook As Workbook
Dim objSheet As Worksheet
Dim lngRowSpace As Long
Dim strName As String
Dim lngTimeRow As Long
Dim lngStartRow As Long
Dim lngInteration As Long
Dim strDataSheet As String
Dim boolError As Boolean
Dim counter As Integer
Dim PctDone As Single

View Replies!   View Related
Split Address Text String Into Separate Columns
I would like to "reverse concatenate" an address text string as follows: ....

View Replies!   View Related
Split Data In A Cell And Get The Value For The Split Data From A Different Table
Here's my problem. I have a cell where there are many data strings seperated by ",". Each data string has a seperate value of its own like for e.g:
A2: aa,ab,ac

String Value
aa 1
ab 1
ac 3

What I want it accomplish is that, split the A2 cell into the different data string entities seperated by ",", then get the corresponding value of each of the data string entity, and to take the average of all the values of the different data string entities.

View Replies!   View Related
Get Data From Several Spreadsheets
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

View Replies!   View Related
Data In Different Spreadsheets
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

View Replies!   View Related
Filter Data In Spreadsheets
I have some data that looks like this:

and in another sheet, corresponding data like this.

I want to be able to filter out the rows from the first sheet when a symbol (kanji) from the right hand side is included at a number higher than that of the second sheet.

For example, 手 appears as a number 6 in the first sheet but doesn't appear until 54 in the second sheet. Therefore I would like to delete the row with 手 in the first sheet.

Is there a simple way to do this? If not, can anyone suggest a way to program this? I was thinking of writing a little VBA code that reads the the characters and then compares them to every number above and if it doesn't find it, deleting that row but I have 2 problems.
1. How can I tell VBA to actually delete a row (not just the contents preferably)
2. These are asian characters which I'm not sure are well supported in strings which I would need for comparison?

View Replies!   View Related
Importing Data To Two Spreadsheets
I have two spreadsheets. One with 1,000 names and addresses, the other with 5,000, names addresses and birthdays.

Within that spreadsheet of 5,000 contain the same 1,000 contacts as the other spreadsheet. How do I import the birthdays of the 1,000 contacts without doing it by hand. Is there a macro or forumla that could do this?

View Replies!   View Related
Import Data From Different Spreadsheets
I am trying to import data from different spreadsheets "about 100" to a principal spreadsheet which will have all the centralized data. I included the principal spreadsheet layout and a sample of two detail spreadsheets. The information that I want to import is from column B 1 to 5 from the detail spreadsheets and put into the principal spreadsheet from row D to G.

Is there any way to import this information automatically, like creating a macro or something similar.

View Replies!   View Related
Copying Data From Spreadsheets
I created a form that takes information from textboxes and places them onto a new worksheet. This is done about 20 times until I have 20 different worksheets.

What I'm looking for is a way to Copy only certain information from all the spreadsheets and paste it onto one spreadsheet. For example if column C has a cell with the word "Not Accepted" in it, that information needs to transfer to a new worksheet.

I'm looking to do this so that it will run through all spreadsheets and grab that specific information and dump it into a new sheet...

View Replies!   View Related
Import Data From Spreadsheets To A Pdf Form
How to import data from excel spreadsheets to a pdf form? I have a PDF form and was wondering if you could have the data from an excel speadsheet automatically go into my PDF form.

View Replies!   View Related
Match And Insert Corresponding Data Across Spreadsheets
Hello, I have a task that would take me hours to complete and I'm sure there's got to be a way to do it in Excel 2007. I need to get part numbers from one master cross reference spreadsheet to individual spreadsheets. The master cross reference spreadsheet has our company 7-digit number AND the manufacturer part number, while the individual spreadsheets only have the manufacturer part number. I need to have the individual spreadsheets match manufacturer part numbers and then insert our company's 7-digit number line-by-line.

Master spreadsheet (snippet):

7-digit PNMFG PN

Individual spreadsheets (snippet):

7-digit PNMFG PN


View Replies!   View Related
Copying Data From Open Spreadsheets
I have about 10 open spreadsheets (the name of these changes each time but has a certain format). What I need to do is write some code to cycle through all the open spreadsheets and copy and paste the data into a single master spreadsheet. All these spreadsheets have a single worksheet.

View Replies!   View Related
Vba Copy And Compare Data In 2 Spreadsheets
I am trying to compare values in column "A" of my PRIMARY spreadsheet with values in column "A" of my SECONDARY spreadsheet. If match in PRIMARY is found in SECONDARY, copy column b and c from SENCONDARY and paste in b and c of PRIMARY. Next, test for next true statement until no other matches in PRIMARY.

Any ideas of how to quickly and efficiently accomplish this task? Actually, I am now copying a spreadsheet from another workbook and pasting the content to another sheet which I am referring to as SECONDARY in the previous paragraph.

View Replies!   View Related
Taking Data From Other Spreadsheets Using A Macro
I have a number of reports in excel (all in the same format) which I need to take certain data out of and store it into one main spreadsheet. Searching through the forums I found some code which I think will do this:

Sub test()
Dim myDir As String, fn As String, ws As Worksheet
myDir = "C: est"
fn = Dir(myDir & "*.xls")
If fn = "" Then Exit Sub
Do While fn ""
Set ws = Workbooks.Open(myDir & fn).Sheets(1)
ws.Range("a7", ws.Range("a" & Rows.Count).End(xlUp)).EntireRow.Copy
ThisWorkbook.Sheets(1).Range("a" & Rows.Count).End(xlUp).Offset(1)
Workbooks(fn).Close False
fn = Dir
End Sub
I have tried using this code, altering the Dir String to the folder the documents are in.

However, when I run the code, nothing happens. I don't get any errors, and nothing appears in the spread sheet.

To be honest, I am not totally familiar with macros in Excel. I can get the gist of what the code is doing having using VB before, but am not totally familiar with some of the functions being used here.

View Replies!   View Related
SUMIF - Pulling Data Across From Different Spreadsheets
To comply with the rules, I have already posted this request in the Excel forums I have 6 spreadsheets that the team that I work for edits on a regular basis. I also have one summary spreadsheet that our director reads and uses for her reports. Most of the forumulae pulling information through are simple = ones, but there is one section which is a bit more complicated. The team have to list the number of client related activities they have done by month - eg:

A------- B--------C--------D---------- E-------F
Month--- Client--ITT-----Quotation-- Demo---Visit
2--------B----------------- 1-------------------2

I am using a SUMIF to total the number of ITTs etc done per month: =SUMIF('S:InternalSales Figures2006-2007[ES 06-07 Spreadsheet.xls]Activity'!$A$3:$A$1001,1,'S:InternalSales Figures2006-2007[ES 06-07 Spreadsheet.xls]Activity'!$C$3:$C$1127)

This works fine if both the Summary Spreadsheet and ES 06-07 Spreadsheet are open at the same time, however, if ES 06-07 Spreadsheet is closed, then all I get in the Summary is #VALUE!. At the moment, all I can think of to rectify this is to do the SUMIF in each separate spreadsheet and then copy the information over to the summary one, which is duplicating information!

View Replies!   View Related
Pull Data From Closed Spreadsheets
I'm trying to extract data from an external spreadsheet, while leaving that spreadsheet closed... I do not know VBA at all, and I'm unsure where exactly to insert code. See my attached file for details, as I wanted to keep this post short, to comply with your rules

View Replies!   View Related
Populating One Spreadsheet From Data In Other Spreadsheets In Same Workbook
I need to have totals from individual worksheets automatically enter into cells in a master spreadsheet.

What I am doing is keeping track of donations collected from individual departments - each on their own worksheet. I would like to have the totals of each page automatically enter & update onto a master worksheet that would show the totals from each dept and then give me a total of all those.

I am not that well versed in Excel. I have been able to set up the individual worksheets and the master.....but can not figure out how to accomplish what I want with the Master tally sheet.

On the individual worksheets I also want to set it up to give me the average donation per person. So if I total the # of donors and the total $$$ amount...what formula do I use to get the average?

View Replies!   View Related
Extracting Data From One Massive Worksheet To Split To Smaller Sheets Of Data
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 Replies!   View Related
Collating Data From Multiple Spreadsheets In A Specific Folder
I have built a timsheet template which I intend to issue to c.140 personnel in my company to fill in for the month of May. On receipt of these timesheets, I will save them all in one, specified folder. I would then like to consolidate the total number of hours worked by adding cell N53 from each timesheet received. Is there any macro/ VB script which can add the contents of a particular cell (N53 in this case) from all the spreadsheets contained within a particular folder?

View Replies!   View Related
Identify The Errors, Duplicates, Typos And Such Between Two Spreadsheets Of Over 4000 Rows Of Data Each
I need to identify the errors, duplicates, typos and such between two spreadsheets of over 4000 rows of data each. The Macro: I got a macro working, but it's not perfect. So far, it can only tell data that's missing on spreadsheets A, or B. However, it can't tell which are the duplicates, typos, etc. Please look at the sample for more details. The code is included in the sample. And for your convenience, it's right here:

Sub difference_general()
Dim frontcount As Long
Dim backcount As Long
Dim diffcount As Long
Dim nosrcflg As Boolean
Dim front_ref As String
Dim back_ref As String
Dim anydiffflg As Boolean
Dim ftnotexistflg As Boolean
Dim invnotexistflg As Boolean
Application. ScreenUpdating = False
Application.Calculation = xlCalculationManual
Cells(1, 1).Select
Selection.Delete Shift:=xlUp
Cells(1, 2).Select............................

View Replies!   View Related
Split 1st & Last Names & Split Addresses After 1st Comma
I'm using Excel 2000 and I have a spreadsheet with 4 columns (A-D) and many (500+) rows.

Part 1:
Colums A & B both contain identical data - a first name and a last name in the format "John Doe".

I want the second word ("Doe") removed from all cells in Column A so that only the first name remains, and I want the first word ("John") to be removed from every cell in Column B so that only the last name remains.

So, where A1 & B1 both started with the data "John Doe" now A1 contains only "John" and B1 contains only "Doe".

Part 2:
Column C contains addresses in the format:
"#5 - 123 Fake Street, Some City, CA 90210"

There is ALWAYS a comma and a space after the street address, then the name of the city or town followed by more data which may include one or more commas.

I would like everything BEFORE the first comma to remain in column C, and everything AFTER the first comma & space to be moved into Column D of the same row. The first comma and space are not needed again.

So, where C1 started with "#5 - 123 Fake Street, Some City, CA 90210", it now only contains "#5 - 123 Fake Street" and D1 now contains "Some City, CA 90210".

View Replies!   View Related
Split Data Within A Cell?
My data within a cell has 3 values separated by commas i.e. abc, def, ghi. I want to separate these values into their own cells. Is there a formula or command that can do this? of course I can go into each cell and cut and paste the values onto another cell, but I have thousands of cells that need to be reformatted.

View Replies!   View Related
Split Data From One Cell...
I'm working on a datasheet that needs some tweaks...

In column A, I have these data
CA 902-20
GA 100-10
UP 200-20
WC 100-20
City #123-89

how can I tidy the data into column B so that they have it will have a standard format of a single letter followed by (no space) 5 digits.
ex: C90220

I am thinking using Left and Right formula, does that sound right?

View Replies!   View Related
Split One Cell Data Into Different Cells
macro of the attached sheet.

Actaully i want a macro which are splits one cell data into deiffernt cells or rows.

View Replies!   View Related
Split Cell Data Into Two Columns
I am trying to split data from a cell into two columns. I have tried the LEFT and RIGHT functions but as the data held is not always the same number of characters this does not work for the LEFT function. The only constant thing is that the last character will always be in the second column. An example of data would be:


and I need to be split into 2 columns as follows:

Column 1 Column2
9065-10 A
9065-9 B
9065-11 C

View Replies!   View Related
Split Data From 1 Cell Into 2 Cells
I have a list of first names and surnames, the first name and surname are in one cell together but seperated by a commer.

For example in A1 would be Steve, Jones

What I want to do is have this split into two new cells. So in A2 I would have his first name and in A3 his surname. Formula to do this is what I cant get to work.

View Replies!   View Related
Split Data In One Column Into Two Columns
For each cell in column A, I am trying to split the data between two new cells. The data in each cell is separated by a 倒. All information to the left of 倒 should go in column B. All information to the right of 倒 should go in column C.

Column A
Aa3 |AAA
A3 |AA-
A2 |A
A3 |BBB+
Aa3 |AA
Aa2 |AA+

Desired Results
Column B.Column C

View Replies!   View Related
Split Data Into Adjacent Columns
I am looking for two formulas (col. B+C) in order to split every second value from Col. A into two adjacent columns. Empty cells, in col. A, should be ignored. The requested result is shown in green and red.

View Replies!   View Related
Split Range Data For CSV File
I've got an Excel document that is generated from a third party programme (See ExcelDoc attached)

I'm trying to manipulate it so that a macro creates a CSV file like the one attached (see THK.csv)

I need to have the following columns in the CSV file populated using the Excel spreadsheet

Column A: Will need to have the NUMBER that is in cell B2 of the Excel file in this case the number is 0714 (need to keep the preceeding 0 too)

Column B: Will need the phone number
Column C: Will need the fax number
Column D: Will need to have the email address
Column E: Will need to have the date only
Column F: Will need to have the Order Number
Column G: Will need to have the Customer name
Column H: Will need to have the Customer telephone number
Column I: Will need to have the Customer alternative telephone number
**Column J: Will need to have the 1st line of the address (after First Address in xls doc)

I've managed to get a macro that will take the information and transpose it into a new document and rename it as an CSV file but I am having a lot of problems trying to get the macro to create the CSV file in the format that I need (see following macro)

Sub CreateCSV1()

Dim wbMyBook As Workbook, iAddPos As Integer

Application. ScreenUpdating = False
Range("A1").PasteSpecial (xlPasteValues), Transpose:=True
Range("A1") = Left(Range("A1"), InStr(Range("A1"), " ="))
iAddPos = InStr(Range("J1"), "TEGKON:")

I've attached the xls document (the one that is generated and the CSV file which is the format I'm trying to achieve.)

**This cell is generated and will have commas at the end of each line of the address and will be terminated by a period (at line 4 of the address. Sometimes there will be 4 lines to an address but sometimes there may only be 2 or three. In the case where there is no information I need to have a blank ( null) value in the cell

View Replies!   View Related
Separate Data At Commas
I've imported a large document to excel. It contains 118 rows of information. My problem is that all the information is in column A. Every cell contains long sentences with information. The information is divided by a comma-sign (,). What i want to do is to seperate the information by columns instead of a commasign. So for example:

1 Div,Date,HomeTeam,AwayTeam,FTHG

......A....B ........C.............D..........E
1 Div Date HomeTeam AwayTeam FTHG

Is there any smart way to do this??

View Replies!   View Related
Summarise Data After Applying Allocation Split
The task involves summarising about 10,000 lines of data into months by Plant by Process. I can achieve it by stepping through a couple of schedules but I'm sure it can be done using one formula (but I can't find it!).

View Replies!   View Related
Split Data Into 2 Cells, Text To Columns
i have cells with city and state in them and i'm trying to separate the one column into two columns. the problem is, i'm trying to keep the city name in one column and the state in the other. some of my cells have two word cities like new albany, or upper arlington. the text to columns feature is separating those cells into 3 columns not 2. is there a way to do this?

worthington, oh
upper arlington, oh

text to columns splitting upper arlington into 3 different cells because the only delimiter in the cell is a space. i need to keep upper arlington in one cell and oh in another.

View Replies!   View Related
Split String Of Data Into Columns And Rows
I am looking for a way to split a large string (400 numeric value's, split by comma's) into a 20x20 field.

If possible, the field should be 600x400 pixels (30x20 squares), but this is just extra
Is there a way to do this in Excel (or any other program)?

View Replies!   View Related
Spliting A Column So 1 Data String Can Split In 2
I am having trouble figuring out how to split the data in a column.

For Example on the attached .xls - the first few records under the SIC column are:


& how i want them to look in seperate columns:

73 790200
59 470104
70 110100
58 1223

I need the first two digits in their own column and the rest of the number in it's own column. I have over 6,000 records, so doing it manually is not a place I want to go!

View Replies!   View Related
Split Cell Data In Multiple Rows
I was just not able to figure out how to write a macro for splitting the cell data into multiple rows with the other column values being duplicated. Just elaborating my problem, its like i have a column in which i have multiple values separted by a comma, So i need to divide all these values on basis of a comma and then copy this value to a new row below, along with the previous column values same for this new row. in my file cost center column is to be worked upon. Can this be done by a macro ?

View Replies!   View Related
Split Data In Single Cell To Other Cells
What I got going on is a program I use for reporting is shooting out information.. while most of the information it sends to an excel workbook is sectioned by cell, there is a good chunk of information that's dumped into 1 cell. Is there a macro, or something, I can do to separate the information in this single cell, into separate cells? Auto Merged Post Until 24 Hrs Passes;Here is an example of the report. You'll notice a bunch of information in the 'Description' section. What I want to be able to do is run a macro that will seperate the 'Item Number', 'URL', 'Serial Number', 'Model Number', and 'Details' information into seperate cells.

View Replies!   View Related
Macro To Separate Data By Date
I am venturing into the unknown with this macro. I am attempting to take a sheet that has already been sorted by name.
It deals with payroll, and the sheet does include the last day of payroll in column F. The actual day worked is in column C.
The problem is that I sum all of the hours worked for each individual person, but I need to exclude hours that are not within the pay week.
Currently they are summed in a new row along with the hours from that week. Does anyone have any suggestions for a macro that could handle this task.

View Replies!   View Related
Separate Sheet Data Collection
I have a separate sheet(Coverage.jpg) that records some data which at this stage has to be entered manually.

The data comes from another sheet(officers.jpg), each row is 1 flight and the days are usually separated by a blank or grayed row.

I was wondering is there a formula that will collect the data automatically.

As you will see on Coverage.jpg it is broken down into Number of flights(per day), how many flights were covered by 2 or more officers and how many covered by 1 officer.. and then the graph generates off the data.

Is there a formula or something that will enter the per day data?

I have just added some false data to show you how some things get recorded.

View Replies!   View Related
Alphabetize Separate Cells Of Data In Same Row
I have a worksheet with 6,500 rows of data. In each row, there are up to 6 cells containing a name in "Last, First" format. I need to alphabetize the data from these 6 cells so they go across the row in order left to right, alpha by last name.

Example of my data layout:

Column 1 Column 2 Column 3
Row 1 Jackson, Bob / Anderson, Lisa / Cardinal, Mike
Row 2 Tomlinson, Steve / Dodge, Sarah / White, Brian

I need them to be in this order:

Column 1 Column 2 Column 3
Row 1 Anderson, Lisa / Cardinal, Mike / Jackson, Bob
Row 2 Dodge, Sarah / Tomlinson, Steve / White, Brian

Is there a formula I can apply that will pull from only the portion of the data before the comma in each cell? Or do I need to separate out all of the names so the first and last names are in separate columns for this to work?

View Replies!   View Related
Find Data In Separate Worksheet
I would like to put together a macro that would compare a list of part numbers against a database, and recommend alternative vendors for the part. The actual VBA I think I can handle, my problem is with the formula, array or whatever is needed to return the results I am looking for.

I have attached a sample workbook to this message that contains two worksheets. The "Sample Database" is an example of the format of the database (in Excel format) that I will be looking against. This data may be a hundred columns wide, and thousands of rows long.

The "Vendor Reference" worksheet shows an example of the data that would be returned if the "perfect" formula were entered into the cells. (I'll explain what I mean by "perfect" in a moment.) It also has an area designated for testing formulas to see what is returned. It's blank, because none of my tests returned anything but errors.

You will notice the column headings on the "Sample Database" worksheet. Columns A and B will always contain the part number and product description. The remaining columns will contain vendor specific data, with perhaps 25 different vendors, and 3 columns per vendor. These columns would be "PV", (for Primary Vendor), the vendor number, (010299, for example), and the Vend Part#.

If there is an asterisk, "*", under the "PV" column for that vendor, that means they are the "Primary" vendor. The cost from that vendor is listed under each vendor number. There may be instances where 2 different vendors are listed as primary. If that's the case, the formula can simply return the FIRST one found.

Here's what the "perfect" formula would return: .....

View Replies!   View Related
Filtering Data Using Two Separate Columns
I have text in column A and text in column B. I would like to create column C and place the number 1 in each row whenever columns A and B contain the text I am looking for. I could then use C to filter all the 1's with ease (by way of macro or the autofilter). In my problem, column A contains the following text per cell:


Column B contains:

I need to filter based on column A having the letter "A", or column B having the word "MATT". Columns A and B do not have to contain both "A" and "MATT", respectively, in order for column C to have a 1.

View Replies!   View Related
Separate Data In Cell Into Columns
how to separate data such as this, into different columns?

85878; null;OMX;OM;2004-09-13 08:58:29.0;691.91;OMX Stock Index;693.01;688.67;691.91;15055;0.0;14.64;0.0;0.0;2004-09-10 00:00:00.0;0.0

Today this data is inserted into one cell, in one column, but in 50 000 rows (in 9 different sheets!). I would like to separe it into separe columns so I could run calculations on the data. Separating it manually would take me at least 1 year,

View Replies!   View Related
Lookup Data From A Separate Sheet
I have a spreadsheet that has a resource table, project stage table and an approx 50 different project sheets. (The 50 sheets are duplicated layouts, just different project names)

Based on the data on the first two sheets I would like to populate the individual project sheet.

For example:

1.In the individual project sheet there are 5 stages in each quarter.
2.The project stage table sheet tells you want stage the individual project is in for the relevant quarters.
3.The resource table sheet tells you how many resources are required for that stage.

Based on this information, I would like to populate the individual project sheet with the information.

E.g. If the project is in the 1st stage, it would then go to the resource table and take number of resources allocated for that stage and populate the 途elevant field in the individual project sheet with the correct value.

I've attached the spreadsheet to hopefully better illustrate this.

View Replies!   View Related
Adding Data To A Separate Worksheet
I have a userform which when you enter data and hit submit, inserts data into a seperate sheet in the same file. Code below

Private Sub CommandButton1_Click()
Sheets("Purchase Order").Select

UserForm1.tbJobNo.Value = _
Sheets("Purchase Order"). Range("I4")

UserForm1.tbJobTitle.Value = _
Sheets("Purchase Order").Range("K4")

UserForm1.tbOrderNo.Value = _
Sheets("Purchase Order").Range("I49")

UserForm1.cbdirector.Value = _
Sheets("Purchase Order").Range("K43")

Instead of placing the data into another worksheet i would like to put the data into a closed xls file named Purchase Order Database.xls.

How can adjust the code for it to do this?

View Replies!   View Related
Split Numbers And Split Words
How do I split numbers!

I have two problems/challenges!

Part I...
I got the answer 1987, and now I want Excel to take the numbers out and display...
1 in one box then i set + in the next, then 9 in the 3ed. box, next box +, then 8, then +, then 7 in the last so that i can have Excel make a SUM of it all to 25.

How do I split 1987 and put the numbers in different boxes?

Part II...
I want to make A=1 B=2... all the way up to 9, then start over again with J=1 K=2... up to 9 again and then over again.

So that if I write my name it comes out as a value of 14 (Odd = O=6 D=4 D=4 =14)

(AJSリ=1 BKTナ=2 CLU=3 DMV=4 ENW=5 FOX=6 GPY=7 HQZ=8 IRニ=9, It's the Norwegian alphabet, that's why there are some extra letters)

So how do I set up my Excel so that is ANY name is typed in I can get it out into a number from the values assign?

View Replies!   View Related
Copyright ゥ 2005-08, All rights reserved