Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Convert data from multiple columns/rows into one column - help!

Hi--

I need some help converting data from multiple rows/columns into one
column. My data looks like this (numbers starting on row 3):
BA BB BC BD BE
2-7-484 2-7-11702 2-7-9690
2-2-9692 2-2-9691 2-2-10289 2-2-8
2-14-4911
2-5-11766 2-5-10638 2-5-11236

and I would like it to look like this in another sheet:

2-7-484
2-7-11702
2-7-9690
2-2-9692
2-2-9691
2-2-10289
2-2-8
2-14-4911
2-5-11766
2-5-10638
2-5-11236

I have a variable number of columns/rows where this data is. But I can
have a cell that holds the number of columns and rows. I would like
blank cells to be skipped.

Any suggestions?

Thanks,
Jason


Post your answer or comment

comments powered by Disqus
Hi,

I have been trying to write a macro to extract data from multiple TRA files into columns in excel.

The data in the TRA file is 6000 rows text delimited with a comma as per quoted. I want to put the data from each TRA file into two columns with the filename as column title. All side by side in one worksheet.

Originally Posted by TRA File 18.082,1.37259 17.8991,1.39007 17.7279,1.40678 Originally Posted by Excel 18.082 1.37259 17.8991 1.39007 17.7279 1.40678
This is what I have so far, it does extract the data and the filename but puts it into one column and leaves one blank column. I'm sure it's something with the redim array but I can't figure it out.


	VB:
	
 
    Dim txt As String, n As Long, x, a() 
     
     ' set folder path
    myDir = "C:UsersmyuserDesktoptest" 
    fn = Dir(myDir & "*.TRA") 
     
    Do While fn  "" 
        n = n + 2 
        txt = CreateObject("Scripting.FileSystemObject").OpenTextFile(myDir & fn).ReadAll 
        x = Split(txt, ",", , vbTextCompare) 
        Redim a(1 To UBound(x) + 1, 1 To 1) 
        For i = 0 To UBound(x) 
            a(i + 1, 1) = x(i) 
        Next 
        Cells(1, n).Value = CreateObject("Scripting.FileSystemObject").GetBaseName(fn) 
        Cells(2, n).Resize(UBound(a, 1)).Value = a 
        fn = Dir 
         
    Loop 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks for any help!

Hi,
I'm looking for some Excel VBA code which will allow me to copy data from multiple workbooks (just the one worksheet is contained in each workbook) into a single spreadsheet (one worksheet). The number of columns will remain fixed. However, the number of rows with data in each workbook will be variable. I would want the data to be copied immediately underneath each other with no empty rows in the single worksheet.

Any help on this would be greatly appreciated.

Thanks.

Hi,
Does anyone know a VBA Macro to return all non-blank values from multiple columns/rows into three columns in the format below in a different sheet?
Example in Sheet1 (2 "header" columns + up to 50 columns of numerical and text data - starting at A2):
Prog 1 12/3/2010 123 234 456
Prog 2 15/3/2010 xyz
Prog 3 16/3/2010 321 abc
would return 3 columns to Sheeet2 (starting at A2):
Prog 1 12/3/2010 123
Prog 1 12/3/2010 234
Prog 1 12/3/2010 456
Prog 2 15/3/2010 xyz
Prog 3 16/3/2010 321
Prog 3 16/3/2010 abc
There are up to 50 columns of the numbers/text (column 3 onwards) and the blanks are always at the end of the rows. The number of rows varies.
Thanks!
janie

Hi all, I am wondering how I can use VBA to extract data from multiple excel files onto one summary workbook that pulls the data from the most recent date. All of the excel files have different names but each worksheet is labeled "Financials". Each worksheet is formatted almost identically - all of the metric names are in column C and the data starts in column D, but not all of the rows are identical. I would like the Summary workbook to be able to reference a formula in order to pull the correct data. A formula that works in each of the workbooks is "=INDEX($D$1:$R$350,MATCH("Debt Service Coverage",$C$1:$C$350,0),MATCH(MAX($D$1:$R$1),$D$1:$R$1,0))". I would simply change the "Debt Service Coverage" to the other corresponding metrics that I am interested in. I am also wondering if I could have this Summary workbook automatically update when opened and have the date that the data is being pulled from for each workbook as it may be different. Thank you in advance!

I often have to move Rows of data from multiple worksheets on to one master sheet, and wondered if anyone had a macro that would go through all of these and do it for me?
I need something exactly like for particular rows ( i no need to copy the entire rows from the sheets)... I have very limited experience with VBA.

Thanks!
Nirmal

Need some help writing code to submit data from several different workbooks into one master workbook. For example: Tom, Doug, and Harry each have their own Excel files with the same column headings. Each time Tom updates and saves his personal file, he is prompted whether or not he wants to update the master file. If he clicks "Yes", the updated entries will be loaded into the master workbook. The same goes for Doug and Harry's, too.

I've seen several codes for RETRIEVING the information FROM the master, but not SENDING the information TO the master. Any tips?

Another added bonus would be for the latest updates to be highlighted in red so they could easily be found. Currently, I'm using the "Highlight & Approve" dialogue, which works OK.

Thanks!

As a real estate practitioner (newbie programmer in excel) I receive various
schedules of vacant properties from different property companies.
In each there are column headings, but the same data in each column of the
various schedules is usually headed by similar (but not identical) labels.
Eg in one schedule the heading may be "Area", and in another it would be
"Size", in still in another it could be "square feet".
To make matters worse the order of the columns may be different, i.e. "Area"
could be in col "C" in one schedule, "Size" could be in col "F" of another,
and so on.
Is there any way I could combine all the data from the various schedules
into one master schedule with column headings and positions of my choice? Or
perhaps there is some freeware out there that can do this?
Thanks in advance.
Stan

As a real estate practitioner (newbie programmer in excel) I receive various
schedules of vacant properties from different property companies.
In each there are column headings, but the same data in each column of the
various schedules is usually headed by similar (but not identical) labels.
Eg in one schedule the heading may be "Area", and in another it would be
"Size", in still in another it could be "square feet".
To make matters worse the order of the columns may be different, i.e. "Area"
could be in col "C" in one schedule, "Size" could be in col "F" of another,
and so on.
Is there any way I could combine all the data from the various schedules
into one master schedule with column headings and positions of my choice? Or
perhaps there is some freeware out there that can do this?
Thanks in advance.
Stan

I have set up timesheets for my department. I have a template that I have
everyone use (About 10 people), This saves the worksheet as "CSTS Employee
number Date serial" For Example if Employee 432 created a workbook for this
week, the file saved would be "CSTS 432 38808.xls". They then mail me this
template every week.
I enter the data from their totals category into a master workbook which has
totals for everyone.
1.) Is there an easy way to pull all of these into this workbook for the
year?
I was going to use the date seial to pull the data into the appropiate
location and tab of the master file.
2.) Can I pull the data from the individule files without having to pull up
the worksheets?
3.) Is there a more eloquent way to do what I want to do?

Everyone uses Office 2003

I have set up timesheets for my department. I have a template that I have
everyone use (About 10 people), This saves the worksheet as "CSTS Employee
number Date serial" For Example if Employee 432 created a workbook for this
week, the file saved would be "CSTS 432 38808.xls". They then mail me this
template every week.
I enter the data from their totals category into a master workbook which has
totals for everyone.
1.) Is there an easy way to pull all of these into this workbook for the
year?
I was going to use the date seial to pull the data into the appropiate
location and tab of the master file.
2.) Can I pull the data from the individule files without having to pull up
the worksheets?
3.) Is there a more eloquent way to do what I want to do?

Everyone uses Office 2003

I have a folder with an unlimited number of Excel Workbooks. Data within workbooks are in same format - 3 columns of data. I would like to extract the Data in the range (B55:B70) for every workbook, transpose the data and append by rows into one worksheet. To be clear - I want to take data range B55:B70 from first workbook,transpose and put on first row - take data range from second workbook, transpose and put on second row, repeating for unlimited number of times...

Hi,

Needed some help to convert data from multiple rows to a single row. Attached is a condensed worksheet, in reality there are about 70 columns and thousands of rows.

Thanks for your help.

******** ******************** ************************************************************************>Microsoft Excel - Book2___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=
ABCDEFGHIJKLMN1Inv.No.OrderQty11020110301104011050110601107011080110901110011110111202IN000020941689 3900156-156000000003IN00002094168950000-3700.25-45.400-590-95.354IN000020951862 275001100-1100000000005IN000020951862500000-25380-368.500-4790-772.56IN0000209518621000000000-40000-1007IN000020962679 277501110-1110000000008IN000020962679500000-25818-26100-340-783-5489IN000020972715 7900-30.380000000010IN00002097271520000000-748.9200-10.711IN000020982592 377501510-15100000000012IN000020982592500000-36226.5-34600-4500-727.513IN000020992612 21600864-8640000000014IN00002099261220000-1636.4-19254.4-136.4000-286.4-286.415IN000021002691 949.1237.96-37.960000000016IN0000210026911000-17.22-896.83-6.3800-8.28-13.39-7.02Sheet1
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Hi all,

I have created a userform within Excel for people to fill in data for a project we're running. Every member has its own file and when it's closed the data in that file gets overwritten. The data is stored on the first sheet, starting from the 2nd row. The amount of data is different in each file as some fill in more than others.

I have created a masterfile with all the links of the project files on the first sheet. On the second sheet I wish to import all the data from the project files. The layout of these files are exactly the same. (only the number of rows with data vary as mentioned above)

Can anyone help me with some VBA code to get all that data into one sheet?

Thank you.

rgds, Marcel

I would like to merge data from 30 tabs in one spreadsheet. They all have the same heading but different number of rows. How can I do it in an effective ways. I got bore just copy and paste data from all the tab into one.

Thanks

Regards
Steve

Hi,

This is my first time on here so I hope someone is able to help me.
I have a workbook with multiple worksheets. I need to copy data from the first 11 columns of 16 of the worksheets & paste the values in a 'loading' worksheet. The worksheets that I need the data from have a similarity in that they are named '**** Pre Load'. Column 6 of the 11 columns is empty but I need the blank column in the loading file. When pasted into the loading file I need it so that the first sheets data will be pasted in cell A2:K10, for example, the next sheet will be from A11:K25 etc.

If anyone can help I would be extremely greatful.

Thanks,
Rod

Hi There,

i got a file with multiple worsheets, i am trying to copy data from all the sheets to one and i got the result. but the issue i am facing is the code copy the data from a specific range and i want to change this as, copy data only if column A has data in each sheets.

for instance,

In sheet1, Column A has data from Row 1 to 4
In sheet2, Column A has data from Row 1 to 3
In sheet3, Column A has data from Row 1 to 4
In sheet4, Column A has data from Row 1 to 2

Presenthly the code is written as copy the data from all the sheets from range("A1:B10") and the code gives the ersult, but i need to change this as Copy data from all the sheets if column A is not blank

ie, the result should be , the data from sheets1 should be from A1 to B4, Sheets2 should be from A1 to B3,

below is the code and attached the xl sheet.

Sub CopyRangeFromMultiWorksheets()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim CopyRng As Range

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Delete the sheet "RDBMergeSheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("RDBMergeSheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "RDBMergeSheet"
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "RDBMergeSheet"

'loop through all worksheets and copy the data to the DestSh
For Each sh In ActiveWorkbook.Worksheets

'Loop through all worksheets except the RDBMerge worksheet and the
'Information worksheet, you can ad more sheets to the array if you want.
If IsError(Application.Match(sh.Name, _
Array(DestSh.Name, "Information"), 0)) Then

'Find the last row with data on the DestSh
Last = LastRow(DestSh)

'Fill in the range that you want to copy
Set CopyRng = sh.Range("A2:D10")

'Test if there enough rows in the DestSh to copy all the data
If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
MsgBox "There are not enough rows in the Destsh"
GoTo ExitTheSub
End If

'This example copies values/formats, if you only want to copy the
'values or want to copy everything look at the example below this macro
CopyRng.Copy
With DestSh.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

'Optional: This will copy the sheet name in the H column
DestSh.Cells(Last + 1, "A").Resize(CopyRng.Rows.Count).Value = sh.Name

End If
Next

ExitTheSub:

Application.GoTo DestSh.Cells(1)

'AutoFit the column width in the DestSh sheet
DestSh.Columns.AutoFit

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

Hi there,

I'm currently still new to Excel VB scripting, but have a fair amount of C/C++ experience. That said, I'm trying to write a script that will move a large amount of information (~400 cells) from 400 worksheets in one workbook (input sheets 1-400) into 1 worksheet in another workbook (output). I recorded an example macro to get some foundation code for what I need to do, however I am not sure how to change the workbook and worksheet name in Formula string.
The example below is the macro code that was automatically generated when I moved some of the averaged data from the input.xls into cells in the output.xls book


	VB:
	
Windows(outputBook.Name).Activate 
 
ActiveCell.FormulaR1C1 = _ 
"=AVERAGE('[Input.xls]12-Jan-05 14h 15m 34s'!R25C2:R26C2)" 
Range("C2").Select 
ActiveCell.FormulaR1C1 = _ 
"=AVERAGE('[Input.xls]12-Jan-05 14h 15m 34s'!R25C3:R26C3)" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Now, I have an array of all of the sheetnames in the inputBook, and I've concatinated the inputBook name and sheetname so its in a similar format to the "[Input.xls]12-Jan-05 14h 15m 34s", by using the commands below.


	VB:
	
 
teststring = "[" & imputBook.Name & "]" & sheetname(inx) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
So, what I want to do is change the formulas above using a variable within the formula, which I thought might look something like this:

	VB:
	
ActiveCell.FormulaR1C1 = _ 
"=AVERAGE(teststring!R25C2:R26C2)" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
However, the code segment immidiately above does not work.

So, does anyone know how to move from multiple worksheets in one input workbook into 1 worksheet in another output workbook? In particular, I was wondering how to change the formula in VB script to be dependent on a variable (teststring) above.

I know this is a complicated question, but if you think I'm going about this the wrong way or have any suggestions, please help me out. I'd greatly appreciate it!

Thanks,
David

How do I display data from multiple worksheets for the same template fields
in one place without physically linking each worksheet's cell to the one
place for each worksheet and cell? I attempted to do this by first putting
the path names to the worksheets in a column. Then I concatenated that
cell's text with the cell position I wanted (ex. $A$3). However, I can't get
Excel to evaluate the contents to display the value in the worksheet's cell
(that is, to link to the value) rather than the path name followed by the
cell name. Any suggestions?

I need to gather information from multiple files that all have the same
layout but different information.

Is it possible to update this information into one master excel file from
the other files???

Regards

Mark Allen

I am willing to pay $20 for this

Here is my situation.

I have a template in Excel sheet with 15 columns. I send this to my 85 co-workers for filling some data in those columns. Once they all send me back the filled Excel file, I will have to collect data from 12 columns (out of 15) and have it in a single word file for all the co-workers. Also, when exporting data from excel sheet to word file, the text shouldn’t be in table format.

Is there a macro for this job to done fast?

I have found this macro from one website. It exports the data from excel to word file in table format and also it cannot export data from multiple excel files to one single word file.


	VB:
	
 CopyWorksheetsToWord() 
requires a reference To the Word Object library: 
     ' in the VBE select Tools, References and check the Microsoft Word X.X object library
    Dim wdApp As Word.Application, wdDoc As Word.Document, ws As Worksheet 
    Application.ScreenUpdating = False 
    Application.StatusBar = "Creating new document..." 
    Set wdApp = New Word.Application 
    Set wdDoc = wdApp.Documents.Add 
    For Each ws In ActiveWorkbook.Worksheets 
        Application.StatusBar = "Copying data from " & ws.Name & "..." 
        ws.UsedRange.Copy ' or edit to the range you want to copy
        wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.InsertParagraphAfter 
        wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.Paste 
        Application.CutCopyMode = False 
        wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.InsertParagraphAfter 
         ' insert page break after all worksheets except the last one
        If Not ws.Name = Worksheets(Worksheets.Count).Name Then 
            With wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range 
                .InsertParagraphBefore 
                .Collapse Direction:=wdCollapseEnd 
                .InsertBreak Type:=wdPageBreak 
            End With 
        End If 
    Next ws 
    Set ws = Nothing 
    Application.StatusBar = "Cleaning up..." 
     ' apply normal view
    With wdApp.ActiveWindow 
        If .View.SplitSpecial = wdPaneNone Then 
            .ActivePane.View.Type = wdNormalView 
        Else 
            .View.Type = wdNormalView 
        End If 
    End With 
    Set wdDoc = Nothing 
    wdApp.Visible = True 
    Set wdApp = Nothing 
    Application.StatusBar = False 
End Sub 

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

hi there,

i read this thread and i am trying to do a very similar thing:

http://www.excelforum.com/excel-prog...ml#post2307276

i have many text files that need to be combined into one text file. the easiest way i can imagine is to use excel to achieve this.

all the text files have only one column and what i want to do is import each text file, take the data and place it ALL into one column on the activesheet.

is there a simple modification that can be done to the code in the above thread to achieve this? or is it simplier to write another vba code?

any help would be appreciated!

thank you very much!

Hope someone can help me - probably a really simple question but something I've never done before.

I have three worksheets which contain data which is laid out identically (position and column labels) in the three sheets. I need to create a report which draws data from the three sheets into one sheet or Pivot Table, for preference. I don't need to use all the columns in the sheets.

I'm using Excel 2011 for Mac.I've Googled this and can find solutions for 2010 Windows Excel involving naming ranges on the three sheets, but am not sure how to go about in 2011, or if I'm even on the right track with a Pivot Table, but I use them to create the sort of report from one sheet that I am looking to create from three.

Hope that makes sense! Can post a sample workbook if that will help.

Hi All,

Wishing you all have a nice time helping to solve problems of many newbie's like me

I need to consolidate data from many excel sheets to one single worksheet.
I have a DSR-Daily Status Report generated everyday listing the resources worked on the defects listed acroos their names. I need to generate a consolidated data at the end of every month having count of defects each resource worked-on. All the DSR(Excel files) are saved in the same folder having their names as "TEM_DSR_28Jan2008" only date changes in this file names. Can anyone of you, write a macro for me to generate a worksheet having all the data in one single file.

Waiting for the favorable reply ASAP, Thanks in advance for sparing your precious time.

Regards,
Guru

I have been given 19,000 seperate excel files, each has the data laid out it
the same way, but i need to get only a few selected pieces of data from all
of them into one file,

i though about a macro, but it only seems to work for the file i created it
in, and i had some issues with information being repeatedly pasted once the
data had been moved,
anyone got any suggestions?


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