Free Microsoft Excel 2013 Quick Reference

Split data in to multiple tabs using value in column

Hello all,

I have a data set in the worksheet "Master" with total of 6 columns at the more than 500 thousand rows. I want your help in splitting this data in to multiple tabs based on the values in column 4. I'm looking at the end result in this way, I have multiple tabs named by the value in column 4 and all the data points (6 columns) related to the name of the tab.


Post your answer or comment

comments powered by Disqus
Hi,

I have macro that which splits data in to different worksheets in the same workbook. Instead of splitting in the same workbook need to split the data into separate workbook and that too replace the existing data on finding whether the workbook (on Name) exists or not. Data split is based on a column (I) value.

On a daily basis I have to work on 80 - 90 workbooks without macro it is hard to rush and thought of getting help from the team.

Work.zip

The above attachment is the Zip file which has Master file and example files. All the workbooks will be in one folder.
Please, could anyone help me.

Hello,

I need help to split data in one cell into multiple rows. I have search some of solutions from this forum, but it doesn't fix my problem.
I have approx 150 workbooks with thousands of data rows as below:

Row/column A D F K N 1 From To Cc Bcc Message ID 15 gb@cd.com ag@bc.com
af@central.com
cf@du.com trs@klo.com
snc@jko.com
kom@pol.com
erw@dh.com jko@yaho.com 1234 30 cn@kl.com dc@uk.com fg@ik.com sg@kl.com 5678 11670 dc@bk.com sg@uk.com
kl@uk.com
df@uk.com
kl@er.com sr@kl.com
ers@yh.com kl@hj.com 7890

Then I would like to combine the column To, Cc, Bcc into 1 column as receiver.

What I've done, I create concatenate formula to join the column.
=Concatenate (D2:D11670, ";", F2:D11670, ";", K1:K11670)

The data can combined.
However, when I tried to split the data using text to column feature, the address can not be delimited by space, semicolon or alt+enter, because it appeared like this:
ag@bc.comaf@central.comcf@du.comtrs@klo.com;snc@jko.comkom@pol.comerw@dh.com;jko@yaho.com

I would like to see the result as like this (example of row 15):

Row/column A D N 1 Sender Receiver Message ID 15 gb@cd.com ag@bc.com 1234 16 gb@cd.com af@central.com 1234 17 gb@cd.com cf@du.com 1234 18 gb@cd.com trs@klo.com 1234 19 gb@cd.com snc@jko.com 1234 20 gb@cd.com kom@pol.com 1234 21 gb@cd.com erw@dh.com 1234 22 gb@cd.com jko@yaho.com 1234

Does anyone can help me to create the macro?
My plan is to create 1 workbook that contain the result table above from the combination of all of the workbooks.
However, if the excel can't handle the data, I would like the macro can be applied for all of workbooks.

Thank you so much in advance.

Marshell

I am trying to extend data validation to multiple sheets but running into issues with the tables being cutoff.

I will first explain what I am trying to do, step-by-step.

1 – I receive a master file from IT. The master file contains customer information for multiple branches.
2 – I need employment information for each customer, which includes Industry Type and Occupation Type (columns I-J). Choices are from a validation table only (columns DA-FC).
3 – Here is where it gets tricky. I am splitting the master file into multiple sheets according to branch number in column A using the following code:
lngLastRow = Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lngLastRow
    lngStoreID = Sheets("Data").Cells(i, 1).Value
    Set MadeSheet = Sheets(CStr(lngStoreID))
    If MadeSheet Is Nothing Then
        Sheets("Data").Copy After:=Sheets("Data")
        ActiveSheet.Name = lngStoreID
        For j = lngLastRow To 2 Step -1
            Application.StatusBar = "Processing Main: " & i & " of " & lngLastRow &
". Processing sub " & j & " of " & lngLastRow
            If Sheets(CStr(lngStoreID)).Cells(j, 1).Value <> lngStoreID And Sheets(CStr(lngStoreID)).Cells(j, 1).Value
<> "" Then
                Sheets(CStr(lngStoreID)).Rows(j).Delete
            End If
        Next j
        Sheets("Data").Activate
    End If
    Set MadeSheet = Nothing
Next i
Creating the sheets works fine. However, the data validation only partially works for each new sheet created. I added this bit of code to add the validation tables to each sheet but it appears that the name definitions are getting all messed up.
 Sub AddIndustryCodes()

    Dim ws As Worksheet
    Dim lngIndex As Long
    
    With ActiveWorkbook
        For lngIndex = .Sheets.Count To 5 Step -1
                Application.DisplayAlerts = False
                With Sheets(lngIndex)
                    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
                End With
                Sheets("IndustryCodes").Range("DA:FC").Copy _
                    Sheets(lngIndex).Range("DA:FC")
                Application.DisplayAlerts = True
        Next
    End With
    
End Sub
There is a reason I am adding the table to each sheet and that is I am using an email application to send each individual sheet to a different person for each branch.

I have spent hours trying to get this working, but no luck. Any assistance is greatly appreciated. Thanks.

I have an XY chart with multiple series. Each series has 96 data points and the location of the maximum value varies within each series (eg it might be point #34 in Series #1, point #76 in Series #2, etc...). I have some code that adds the series name as a data label to the 96th point in each series which works fine. What I would like to do is to be able to add this same series name data label to whatever the maximum value point is for each individual series. This will stop me from having to move all of the labels manually so they sit on top of each data series.

Code snippet below:

	VB:
	
 LastPointLabel() 
    Dim mySrs As Series 
     'Dim nPts As Long
    For Each mySrs In ActiveChart.SeriesCollection 
        With mySrs 
             'nPts = .Points.Count
            mySrs.Points(96).ApplyDataLabels Type:=xlDataLabelsShowValue, AutoText:=True, LegendKey:=False 
            mySrs.Points(96).DataLabel.Text = mySrs.Name 
        End With 
        Selection.AutoScaleFont = False 
        With Selection.Font 
            .Name = "Arial" 
            .FontStyle = "Regular" 
            .Size = 8 
            .Strikethrough = False 
            .Superscript = False 
            .Subscript = False 
            .OutlineFont = False 
            .Shadow = False 
            .Underline = xlUnderlineStyleNone 
            .ColorIndex = xlAutomatic 
            .Background = xlAutomatic 
        End With 
    Next 
    For i = 1 To 32 
        With ActiveChart.SeriesCollection(i) 
            With .Points(96) 
                With .DataLabel 
                    .HorizontalAlignment = xlCenter 
                    .VerticalAlignment = xlCenter 
                    .Position = xlLabelPositionAbove 
                    .Orientation = xlHorizontal 
                End With 
            End With 
        End With 
    Next i 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any thoughts?

Hi everybody,

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.

Thanks

Larry

Splitting data in Work sheet1 to several sheets.

Where there is a change in the data in the column 1 data the data should be moved to the next work sheet.
Please advice any macro for this.

Thanks in advance
Santanu

Hi guys,
I need some help please! I have a spreadsheet with multiple worksheets in it.

The worksheets all have a column with values like

LOCAL_TEST_GROUP_001_002
LOCAL_TEST_GROUP_001_003
LOCAL_TEST_GROUP_001_009

(for above the worksheet name would be GROUP)

the next might be worksheet will have something like:

LOCAL_TEST_PHONE_001_002
LOCAL_TEST_PHONE_001_003
LOCAL_TEST_PHONE_001_009

the worksheet name for that would be PHONE. and so on etc.

What I need to do is split this workbook up in to multiple workbooks. Each sheet in the main workbook becoming a new workbook (named after the sheetname).

On top of that I need to have mulitple worksheets in these new workbooks which will contain (take the last example)

A different worksheet for all entries with the following contained in them.
LOCAL_TEST_PHONE_001_002
LOCAL_TEST_PHONE_001_003
LOCAL_TEST_PHONE_001_009

Their names will be PHONE_001_002 etc. (no LOCAL_TEST)

Hope I'm being clear but it's quite difficult to describe!

Im trying to find out if excel can do something pretty different, or maybe that my excel knowledge is just very lacking which is very possible. I have a main sheet with a lot of data on it. I want to try to have excel pull data from that main sheet and transfer all the information that goes with that row to another sheet, using one column as the reference point. In other words if column k is my reference line. If the data for one row in column K is "A" I want it to transfer to sheet 2 with all data from that row. If the data for another row in column K is "B" I want it to transfer to sheet 2, and so on with a few reference points.I dont know if this is possible. Any help would be greatly appreciated.

Hi,
I have a excel file in which I have data which is sorted date and language wise.
I have created 3 new sheets with names "English", "Hindi" and "English & Hindi".
I want to split data in Raw Data sheet to all the three sheets date wise.
I will add data date wise to raw data.
If the date in english is may 1 to may 15 then it should copy the all the rows related to may 1 to may 15 in English sheet from Raw Data.
same like Hindi and English & Hindi.

Attaching the file for reference...
Please help

Hi,

I am trying to pull data from multiple tabs into one summary sheet. However the data in the multiple tabs is by column and the summary sheet is by row.

For example, in the summaryu sheet i have as follows

Sales - New York
Sales - Dubai
HR - New York
HR - Dubai

in the tabs I have tables with rows as follows:-

Sales
HR

Columns are by City
New York
Dubai

In these tables there is the data I want to pull over into the summary

I am setting up a spreadsheet and would like to be able to have multiple tabs
within a column. Is this possible and how do I set the tabs? This would be
use for setting up a financial statement or something similar. Thanks for
the help.

How to export data in to excel from sql server?

How do I compare data in two different spreadsheets i.e. (column 1 against
column 1)?

I am looking for a way to equate the value of the cell with a cell in numerous other tabs, using a column of data to reference the tab name.

For example:
Column A has values 1 through 10
Column B has formula like ='A1'!$B$10, where A1 is the tab name.

Excel won't let you input the cell reference as the tab name. Is there another way to do this? The goal is to get the value of B10 from all other tabs, going down column B in the current tab.

Thanks for your help.

Hi friends....
I want to split the semicolumn separated data in a column which has been imported from .csv file into multiple columns. i.e the data in column M is clients;report;excel in .csv file and when exporting to excel it has to be split into 4columns that is also specified by the user like A,E,R.. so clients has to be placed in A report in E and Excel in R. Please help. i have been trying to solve this from 2weeks..
The code which iam using is:
Sub Splitcolumns()
    Dim a As String
 a = Worksheets("Job Listing").TextBox1.Value
    Rem This refers to the column named/labeled A
    
    ActiveSheet.Columns(a).Select
    
    Dim c As Range ' Represents a single cell
   Dim bNext As Boolean
   Dim arrSplitText As Variant
  Const gsCOLON As String = ";" ' Colon Text delimiter
 '--
 Application.ScreenUpdating = False
 For Each c In ActiveSheet.Columns(a).SpecialCells( _
 xlCellTypeConstants)
 If bNext Then
 bNext = True: GoTo LabNextCell '>>>
 End If
 
  arrSplitText = Split(c.Value, gsCOLON)
 ' Update columns
 c.Next.Resize(1, UBound(arrSplitText) + 1) = arrSplitText
LabNextCell:
 Next c

End Sub


I need help with the VBA code to split contents of a cell in colum D into multiple rows while copying the data in columns A, B, C, E, F and G.

The data is separated by a semicolon (.

Is there a way that I can use data from a cell to reference a worksheet?

I have two columns with data in.

The first column (called columnA) contains values which match the names of
my worksheet tabs. The second column (columnB) contains references to items
found in those tabs.

I want to be able to use the first colum to speciy which tab to look in and
the secound column to be able to use the VLOOKUP function to then grab data
relevant to that entry. I can do the second bit but the first is proving
very difficult.

I need to somehow use the data contained in a cell to reference a worksheet
tab?!

Hello all...

I'm trying to find out if there is a function that will allow me to sum the values for a designated set of critieria that may have one or more values in a cell.

Attached is a spreadsheet that contains two worksheets, [data] and [target].

The [data] tab contains the data set to be used with two columns, (code) and (balance).

The [target] tab contains the results of my "query" with three columns, (code set), (month), and (value).

In the [target] tab, the (code set) column contains the criteria I want to "filter" the data set by. sometimes there will only be one criteria identified. But other times, there may be two or more criteria identified and separated by commas.

In the [target] tab, the (value) column and first two rows, I've used the SUMIF function where if the "one" identified criteria is found it will total the sum.

However, for the last row, how do I get the SUMIF function to recognize that there are multiple criteria values that need to be found then summed?

Any guidance would be greatly appreciated.

ML

I have a column of data with a variety of identifier codes. In each row next
to the ID codes is data. There are several tabs within my workbook with
similarly constructed data bases. Can I use Sum Product to summarize the
data across the multiple tabs that match each of the id codes? If not, is
there another way?

I have a template work sheet that contains several cells that hold multiple
pieces of data. One of the things that I have to do with each cell is sort
the data in the cell into alphabetical order. I understand I have to first
break the data out into individual cells and then sort thos cells. My issue
is the template needs to be set up so that the data can be pasted from a .csv
file so Iu do not have the ability to use the "text to columns" feature. I
need the formula to split this data already set up and ready to go. An
example of the data is as follows:

CII CTI DDI DE TEUP BIU2 XWUU AAU

There can be as few is 1 data item or as many as 100 in each cell. Then
going down the column there can be as few as 1 cells in the column or as many
as 1000 cells. Each cell should be treated as an individual issue and there
is plenty of room to the right for breaking the cell out.

Does anyone have any suggestions??

Trading Add-in For Excel|| Convert/Migrate Databases
Hello,

I need help ASAP to split data in one cell into multiple rows. I have search some of solutions from this forum (tried some macros from this forum), but it doesn't fix my problem. I would pay £20 via Paypal.
I have approx 150 workbooks with thousands of data rows as an example below:

Row/column A D F K N 1 From To Cc Bcc Message ID 15 gb@cd.com ag@bc.com
af@central.com
cf@du.com trs@klo.com
snc@jko.com
kom@pol.com
erw@dh.com jko@yaho.com 1234 30 cn@kl.com dc@uk.com fg@ik.com sg@kl.com 5678 11670 dc@bk.com sg@uk.com
kl@uk.com
df@uk.com
kl@er.com sr@kl.com
ers@yh.com kl@hj.com 7890 Then I would like to combine the column To, Cc, Bcc into 1 column as receiver.

What I've done, I tried to create concatenate formula in a workbook to join the column.
=Concatenate (D2:D11670, ";", F2:D11670, ";", K1:K11670)

The data can combined.
However, when I tried to split the data using text to column feature, the address can not be delimited by space, semicolon or alt+enter, because it appeared like this:
ag@bc.comaf@central.comcf@du.comtrs@klo.com;snc@jko.comkom@pol.comerw@dh.com;jko@yaho.com

I would like to see the result as like this (example of row 15):

Row/column A D N 1 Sender Receiver Message ID 15 gb@cd.com ag@bc.com 1234 16 gb@cd.com af@central.com 1234 17 gb@cd.com cf@du.com 1234 18 gb@cd.com trs@klo.com 1234 19 gb@cd.com snc@jko.com 1234 20 gb@cd.com kom@pol.com 1234 21 gb@cd.com erw@dh.com 1234 22 gb@cd.com jko@yaho.com 1234

Does anyone can help me to create the macro?
My plan is to create 1 workbook that contain the result table above from the combination of all of the workbooks.
However, if the excel can't handle the data, I would like the macro can be applied for all of workbooks.

Please let me know if the question is not clear enough.
Thank you so much in advance for your help.

Marshell

I simply don’t have the needed time to research and create this code so I hope someone here will be willing to take the job.

I have a few hundred emails that I need to extract the data from and toss it into Excel; a sample of the email is listed below.


	VB:
	
[FONT=Tahoma]Time Zone (GMT -08:00/-07:00) Pacific Time[/FONT] 
[FONT=Tahoma]To John, Smith[/FONT] 
[FONT=Tahoma]Sent 22-APR-2011 08:55:14[/FONT] 
[FONT=Tahoma]ID 2414512[/FONT] 
 
[FONT=Tahoma]An ASL update has been made requiring QA approval. Please see details below.[/FONT] 
 
[FONT=Tahoma]An ASL update has been made requiring QA approval. Please see details below.[/FONT] 
 
[FONT=Tahoma]Item ............................. 75009[/FONT] 
 
[FONT=Tahoma]Vendor ........................ JACKSON IMMUNORESEARCH LABORATORIES INC[/FONT] 
[FONT=Tahoma]Vendor Site ...................GMP-NJ[/FONT] 
[FONT=Tahoma]Vendor Type ................ VENDOR[/FONT] 
[FONT=Tahoma]ASL Using Org ..............N/A[/FONT] 
[FONT=Tahoma]ASL Owning Org ...........DNI[/FONT] 
 
[FONT=Tahoma]PO Number .................. [/FONT] 
[FONT=Tahoma]PO Line Number .......... [/FONT] 
 
[FONT=Tahoma]ASL Status OLD ...........N/A[/FONT] 
[FONT=Tahoma]ASL Status New ..........Pending[/FONT] 
 
[FONT=Tahoma]Last Update By .............MFAIT[/FONT] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
There is also an .htm attachment that I would like to extract a URL from and add it into the same Sheet. I attached the files code below:


	VB:
	
 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

	VB:
	
[/FONT] 
[FONT=Tahoma][/FONT] 
[FONT=Tahoma][/FONT] 
[FONT=Tahoma][/FONT] 
[FONT=Tahoma][/FONT] 
[FONT=Tahoma][/FONT] 
[FONT=Tahoma][/FONT] 
[FONT=Tahoma][/FONT] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I need to be able to select the folder that holds the emails as it is possible that this might change. Other than that its simple put the data into its own colums and include the link from the .htm file.

Let me know if you need anymore information.

EDIT: Not sure why but its adding in another Code tag, sorry about that the .htm code is all in one doc and is not split.

I have a directory containing the file names of excel sheets based on a "team name" and "week commencing date" examples (there are 24 different teams but only 2 shown here for simplicity):

FR1-12.02.2009
RA12-12.02.2009
FR1-05.02.2009
RA12-05.02.2009

Within these files I have a sheet containing the data called "dump" which I wish to import in to a workbook which i use to carry out analysis from.

I import the data in the following manner:

The following code looks up the files within the directory upon opening the workbook and prints them as a column in Sheet1.
Private Sub Workbook_Open()



get_files



End Sub


Sub get_files()

Extension = "*.xls"
folder = "C:UsersChrisDesktoptester"
First = True
RowCount = 1
Do
If First = True Then
FName = Dir(folder & "" & Extension)
First = False
Else
FName = Dir()
End If
If FName <> "" Then
Range("Sheet1!A" & RowCount) = FName
RowCount = RowCount + 1
End If
Loop While FName <> ""

End Sub
I then convert this column of data in to a list box in Sheet2 which a macro then uses to open the file selected when i click the button "load" and copies the data from sheet titled "dump" from the specified file and pastes it in to my analysis workbook in to the sheet "dump" using the following code:

Sub Button2_Click()


    Application.ScreenUpdating = False
    On Error Resume Next
 
    'Call the macro GetRange
    GetRange Range("B10"), Range("A1"), "Sheet2", "$A$1:$AH$527", _
             Sheets("Test").Range("A1")
 
    On Error GoTo 0
    Application.ScreenUpdating = True

End Sub

Sub GetRange(FilePath As String, FileName As String, SheetName As String, _
             SourceRange As String, DestRange As Range)

    Dim Start
 
    'Go to the destination range
    Application.Goto DestRange
 
    'Resize the DestRange to the same size as the SourceRange
    Set DestRange = DestRange.Resize(Range(SourceRange).Rows.Count, _
                                     Range(SourceRange).Columns.Count)
 
    'Add formula links to the closed file
    With DestRange
        .FormulaArray = "='" & FilePath & "/[" & FileName & "]" & SheetName
_
                        & "'!" & SourceRange
 
        'Wait
        Start = Timer
        Do While Timer < Start + 2
            DoEvents
        Loop
 
        'Make values from the formulas
        .Copy
        .PasteSpecial xlPasteValues
        .Cells(1).Select
        Application.CutCopyMode = False
    End With
End Sub

What I wish to be able to do is have the "dump" sheets from each file load in to my analysis "dump" sheet dependent on the date entered in to a cell which will be a reference to the filename of the workbooks. I can then modify this when needed to do the same for team based analysis instead of weeks. How would i modify my code to do this? I assume i will run a macro similar to the one above but it will search the file names before loading them in to a second dump sheet? I would like each files data to load in this dump one below the other as at the moment the macro simply pastes to A1 but this would overwrite if i load multiple sheets so needs to be offset somehow.

Very hard to explain i know but any help is appreciated and any ideas on how to do this easier. But i am essentialy looking for a modification to the macro pasted above to copy and paste multiple sheet data below eachother in to one sheet based on criteria entered in to a cell.

I have a template work sheet that contains several cells that hold multiple
pieces of data. One of the things that I have to do with each cell is sort
the data in the cell into alphabetical order. I understand I have to first
break the data out into individual cells and then sort thos cells. My issue
is the template needs to be set up so that the data can be pasted from a .csv
file so Iu do not have the ability to use the "text to columns" feature. I
need the formula to split this data already set up and ready to go. An
example of the data is as follows:

CII CTI DDI DE TEUP BIU2 XWUU AAU

There can be as few is 1 data item or as many as 100 in each cell. Then
going down the column there can be as few as 1 cells in the column or as many
as 1000 cells. Each cell should be treated as an individual issue and there
is plenty of room to the right for breaking the cell out.

Does anyone have any suggestions??


No luck finding an answer? You could always try Google.