Split Data Into Separate Spreadsheets

Sep 25, 2008

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

ADVERTISEMENT

Creating Separate Reports In Separate Spreadsheets

Nov 21, 2006

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 9 Replies View Related

List Days Of Each Month On Separate Spreadsheets?

Feb 22, 2012

I need to list the days of each month on separate spreadsheets...and need to to do this each year...

I was wondering if in cell A1 I type the first day of the month then is there a formula I can use to put the rest in without having to manually put them all in?

View 6 Replies View Related

Identifying Matching Rows Between 2 Separate Spreadsheets

Aug 11, 2006

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 3 Replies View Related

Splitting Data Into Separate Spreadsheets Based On Column Values

Dec 18, 2013

I have a data that has several columns. But I need to separate the spread sheet based on one particular column values.

View 14 Replies View Related

Comparing Character Strings In Separate Cells In Excel Spreadsheets?

Apr 1, 2014

I have a large excel spreadsheet with alpha-numeric data. I want to be able to compare two cells in different row side by side and return the difference in another cell.

e.g. I have in cell B2, "tom, rick, mike, I" and in cell C2, "mike, rick". I need to compare the cell C2 to cell B2 and return the difference in cell D2 which in this case would be the characters " tom, I". They are separated with "," and they can be in different order as you can see in the example.

View 2 Replies View Related

How To Split Numbers Into Separate Column

Jun 22, 2013

I have a list of user names and UIDs, all in one column. I'd like to split the numbers into a separate column. How can I do this? Here's an example:

A'Aross Hd 798027047

A'Yolanda Gallegos 100004367799914

Aaiky Sweet 100000984883871

Aarchie Alin 100000295447271

Aasusana Azanza 100000086329219

Ab Raf 100000223369007

Abigail Cadenas 100003769100097

Abigail Gopaul 100002988007633

Abod Rezk 100002010187332

Btw, I'm an Excel newb and don't know a lot. I did try Data/Text to Columns with a delimiter of spaces, but there's really no rhythm with the spaces so it splits it all whacky.

View 9 Replies View Related

Split Data Into Separate Workbooks

Sep 26, 2013

I would like to separate data into multiple workbooks based on a unique value in the column. For instance if I have Departments in Column F, that has data of accounting, HR, etc. I would like to put all accounting data into a accounting workbook and all HR data in a HR workbook.

View 4 Replies View Related

How To Split The Content Of A Cell Into Separate Columns

Aug 12, 2014

In A1 I show the following content: 5,12,27,36,124

How can I make this split to show the following layout:

A1: 5,12,27,36,124 B1:5 C1:12 D1:27 E1:36 F1:124

My worksheet has thousands of lines so hoping for a quick and easy formula that I can pull down and copy for the entire sheet?

View 7 Replies View Related

Split The Date And Time Into Two Separate Columns?

Aug 20, 2014

After I imported this data, the date and time is in the same column in the format of "mm/dd/yyyy hh:mm:ss" military time. How do I write a VBA code to split up the date and time into two separate columns. One column would only have "mm/dd/yyyy" while the other only have "hh:mm:ss" in military time.

Eventually, I need to extract information from the data by looking for a specific time. I would also plot time vs something.

I don't know if treating it as a string would work, because it would just become a text rather than a time, right?

View 10 Replies View Related

Macro To Split Workbook Into Separate Workbooks

Jul 7, 2009

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 12 Replies View Related

Macro To Split Data Into Separate Worksheets

Oct 30, 2010

I have a macro below which splits the data in my workbook (Attached) into seperate workbooks on the basis of each change in data, and saves the new workbooks with the value available in column A. Everything else works perfectly with this code I just want the code to take the workbook name from Column B, not Column A as it is currently taking.

Sub Test()
Dim Sh As Worksheet
Dim Rng As Range
Dim c As Range
Dim List As New Collection
Dim Item As Variant

[Code] ......

View 9 Replies View Related

Split Text - Separate Suffix From Various Addresses

Aug 7, 2012

Is there a way to separate the suffix from various addresses i.e

A1= FOSTER AVE
A2=WISE
A3=EL DORADO ST
A4=OLD HIGHWAY 221

Some of the addresses are up to 5 words and numbers, some are missing the suffix all together.

View 6 Replies View Related

Split Address Field Into Separate Columns

Apr 26, 2007

I have a spreadsheet with a few thousand rows, yeah i know..lol 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 9 Replies View Related

Split Large Range Into Separate Worksheets

Aug 28, 2009

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 9 Replies View Related

Separate/Split Data In One Cell To X Cells

Jun 3, 2008

How do I split data in one cell into three cells?

example:

From

(A1)100 CARIBBEAN VILLAGE DR

To

(B1)100
(C1)CARIBBEAN VILLAGE
(D1)DR

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

View 4 Replies View Related

Split Data Based On 3 Columns Into Separate Worksheets?

May 22, 2014

I need to split the data into worksheets (see attached) by the Advertiser column and then by the deal year and deal code columns. I need each worksheet to be named per advertiser and deal year_deal code. I took off and replaced the data since it is sensitive information.

creating a macro or implicating one into the workbook to run for future reporting.

View 13 Replies View Related

Split Data Into Separate Worksheets Based On Column C?

Apr 11, 2012

I am looking to split data in a worksheet into separate sheets based on Column C which contains Either Babycare or Homecare or Industrial or Professional. Eventually there maybe more names. If possible I would also like to name each sheet.

My VBA skills is limited to this particular task.

View 2 Replies View Related

Split Date / Time Strings To Separate Cells

Jul 15, 2014

splitting out date/time strings to separate cells, but I cannot find how to split the following.

I have the serviceable date of an aircraft delivery set as 'S hh:mm dd-mmm-yy', which is due to being used by an external application in this format. This is entered by the user and I need to be able to extract the date time strings and switch them to dd-mmm-yy hh:mm, excluding the S from the result as a formula in another cell.

I keep getting a #value error or returning the whole string result.

View 3 Replies View Related

Split Address Text String Into Separate Columns

Mar 13, 2009

I would like to "reverse concatenate" an address text string as follows: ....

View 9 Replies View Related

Can Split Currency Into Two Separate Columns Showing Pounds And Pence?

Dec 1, 2012

I have a mileage expenses form from work that I am trying to fill in using Excel, instead of filling it in by hand.

Column R shows the miles travelled.

Column S shows the Rate per Mile.

Column T (should) show the pounds.

Column U (should) show the pence.

Searching has led to T11, for example, using the formula below to show the pound value.

Code:
=INT(R11*(S11/100))

However, I am having less luck on finding a way to display the pence amount in U11.

Ideally, I would like the cells in column U to show the pence without a leading zero or decimal point and just the pence value only.

View 7 Replies View Related

Split Concatenated Delimited Cell Values Into Separate Columns

Jun 20, 2014

Im trying to come up with an automated way of splitting concatenated information but putting each concatenated value in a seperate row.( i have columns of data that need to stay with each concatenated value) Eg

Concatenated comma delimited data

Cust Name Cust Identifier Locations
L Kim 543545 California,Chicago,Florida
S David 31434 Maryland,Vermont,Maine
D Bryant 572575 Texas,Oaklahoma,Nebraska

i need to to get a result that looks like the below example im currently just doing text to column filterting and copying all the columns over and stacking them on top of each other. i need to find a way to automate this process but i cant seem to think of one

Customer name Customer Identifier Location
L Kim 543545 California
L Kim 543545 Chicago
L Kim 543545 Florida
S David 31434 Maryland
S David 31434 Vermont
S David 31434 Maine
D Bryant 572575 Texas
D Bryant 572575 Oaklahoma
D Bryant 572575 Nebraska

View 2 Replies View Related

Create Subfiles From Master File Using Key To Split Workbooks To Separate Files

Jun 13, 2013

I am trying to make my macro more dynamic for a spreadsheet I am working on. I have this code which splits out worksheets (using the specific names of the worksheets-not dynamic) from a main workbook into template workbooks (which are basically just an empty workbook because I didn't know how else to do it) and then saves them using the paths below. I would like to make this more dynamic by splitting the different worksheets into new workbooks based on a key column in the hierarchy worksheet.

Basically I have a list of accounts in column B with the file name they should be exported to in column A. There are about 30 accounts being split into 6 different region files. Also note that the same account might be listed multiple times in column A (needed to add multiple numbers for other lookup formulas in the worksheets) but that account worksheet will still only be going to one of the six region files and not to multiple regions. After these are copied to an individual file I would like to save it to a location on my computer. All files will go to this location.

VB:
Sub Create_Subfiles()

Dim FDMName As String
Dim FBName As String
Dim DIYName As String
Dim WMName As String

[Code] .....

View 3 Replies View Related

Macro To Split Excel File Into Separate Workbooks And Automatically Email It

Jan 10, 2014

I need to write a macro to split an excel file into separate workbooks and automatically email our project managers.

View 1 Replies View Related

How To Split Text From Text String Into Separate Columns - No Delimiters

Apr 8, 2014

I have the cell data as below

How would I split into a new column the first part which is a date into a new column, then the country and the remainder into separate columns?

I still want the original data as I need to check that the splits worked well?

16.5.90 CH 1671/90-4
18.10.1991 CH 3056/91-1
24.07.92 ch 2341/92-2
30.7.92 ch 2395/92-3
18.11.92 Us 3533/92-5
26.5.93PCT 1577/93-0
9.8.93 CH 2363/93-8
17.8.93 CH 2445/93-0
25.1.94ch209/94-6;8.12.94ch3714/94-1
25.1.94 ch 209/94-6 ; 8.12.94 ch 3714/94-1
8.4.94 ch 1047/94-0
22.4.94 ch 1255/94-7
18.11.1992 CH 3533/92-5
18.11.1992CH 3533/92-5

View 2 Replies View Related

Split 1st & Last Names & Split Addresses After 1st Comma

Mar 6, 2008

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 6 Replies View Related

Split Numbers And Split Words

May 8, 2009

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 14 Replies View Related

Shade A Cell Red If Two Separate Conditions Apply (in Separate Cells)

Dec 3, 2013

I'm trying to use a formula in conditional formatting to highlight a cell red if the cell contains a 0 but the date shown in another cell has passed. I want to copy the formatting throughout a column but I don't want the cell to highlight if there is no date in the other cell concerned.

View 11 Replies View Related

Separate Letters And Numbers In A String Into Separate Columns

Nov 20, 2012

I receive an extraction from AutoCAD that lists the electrical devices in a drawing. I don't have any problems extracting the letters. I have a problem extracting the device number and the device number extension.

The device label extraction is similar to this:
DCM1005-1
DCM1005-10
DCM1005A
MTR1005-1
MTR1005-10
MTR1005A

I want to create 3 columns from the device label: (I separated the column with commas)
A1, B1, C1, D1
DCM1005-1, DCM, 1005, 1
DCM1005-10, DCM, 1005, 10
DCM1005A, DCM, 1005, A
MTR1005-1, MTR, 1005, 1
MTR1005-10, MTR, 1005, 10
MTR1005A, MTR, 1005, A

View 5 Replies View Related

Separate Numeric / Text Combination Into Two Separate Columns

Oct 9, 2013

How can I separate the following numeric/text combination into two (2) separate columns in Excel?

302ALTO
406AMZN
451AMRC
404AMAD
605ANCC
405ADRC

The result would be:

302 ALTO
406 AMZN
451 AMRC
404 AMAD
605 ANCC
405 ADRC

View 6 Replies View Related

How To Separate Text From Numbers Into Two Separate Cells

Feb 13, 2014

I'm trying to separate text from numbers into two separate cells...

Essentially, I would like the users to copy and paste data into Column A, as seen below. Then, hopefully by formula separate the text characters into Column B and the numbers into Column C.

Input: Output 1: Output 2:

Col A Col B Col C
Wells 123 Wells 123
Wells 1234 Wells 1234
Wells Fargo 123 Wells Fargo 123
Wells Fargo 1234 Wells Fargo 1234
Wells Fargo Inc 123 Wells Fargo Inc 123
Wells Fargo Inc 1234 Wells Fargo Inc 1234

Ideally, I would like to do this with a formula...

View 6 Replies View Related

Separate Workbook By Value In Column Into Separate Workbooks

Feb 25, 2013

I have 10 very large workbooks that are all setup in the same format. In column Z is a numerical value from 1 to 83. I have been trying to filter the sheet and then copy one at a time from 1 to 83 but that takes a LONG time especially when there is 10 workbooks to do.

Is there anyway I can run a function or macro or something that would just automatically look down the column Z and put each row into a it's own workbooks?

I have attached a sample of what the workbooks look like right now.

Sample123.xlsx‎

View 4 Replies View Related

Split Data In A Cell And Get The Value For The Split Data From A Different Table

Jul 31, 2006

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 9 Replies View Related

How To Separate Row Data From One Workbook Into Separate Workbooks Based On Cell Data

Apr 23, 2014

I need to separate row data from one workbook into separate workbooks based on cell data. Currently I filter the data, copy and paste it to a new workbook and save it. It's a tedious process and was hoping to find a way to automate it. I have attached a sample file. In this instance, I would like a separate workbook for the filtered data in Column 1 and then all the row data gets copied to new workbook. So all of Pennsylvania data would get copied to new workbook, then all of the Michigan data gets copied to a new workbook. I have also attached an example of the end result that I need.

View 9 Replies View Related

Turn The F1 Key Off In Spreadsheets

Dec 5, 2008

I'm going to turn the F1 key off in my spreadsheets so that I do not hit it by accident so manytimes, but how do I bring up help without it?

View 5 Replies View Related

Combine Several Spreadsheets Into One

Jun 24, 2009

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?

View 6 Replies View Related

Joining Two Spreadsheets

Nov 13, 2009

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.

For example:

A:
personel number1
personel number2
personel number3

B:
personel number1, 'sally'
personel number1, 'john'
personel number3, 'pete'

merge A & B:
personel number1, 'sally', 'john'
personel number2
personel number3, 'pete'

View 5 Replies View Related

Merge 2 Spreadsheets?

Nov 23, 2009

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."

View 4 Replies View Related

Linking Two Spreadsheets

Aug 5, 2008

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.

The workbooks are attached below.

View 12 Replies View Related

Data In Different Spreadsheets

May 21, 2007

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 14 Replies View Related

Comparison Of Two Spreadsheets

Mar 6, 2009

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

View 15 Replies View Related

ADVERTISEMENT