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

Free Microsoft Excel 2013 Quick Reference

Cut data from a varying number of consecutive columns and paste into rows below

Hi,

I have data set out as follows:

In column A I have a list of items: Many items are repeated anything between 2 and 12 times (i.e. they appear consecutively on up to 12 rows). Each item is highlighted bold for the first instance of that item to serve as the title.
In columns B, C, D, E, F, G etc I have costs for the items.

The question is how can I cut and paste the costs (the data in columns B, C, D, E, F, G etc) into column B? What makes this more complicated is that it's not a case of simply cutting and pasting down a row:
Say I'm dealing with an item in A1: If there were 3 instances of this item then I'd leave the first instance of the data relating to A1 in B1, then cut from C1 and paste into B2, cut from D1 and paste into B3. Then I'd move on to the next item of data which would be in A4, with the data relating to it in B4, C4, E4 etc.

The attached file shows this more clearly.

When the macro encounters a zero it signifies that the end of the data for that item has been reached and should start with the next item of data.

There are different numbers of columns in use for each item of data varying between 1 and up to a potential maximum of 12.

Thanks very much for any help, it would be greatly appreciated.WERV_Example_CUTPASTE.xls


Post your answer or comment

comments powered by Disqus
I am trying to consolidate totals into one workbook from a varied number of
other workbooks that are saved in the same folder.

All workbooks are identical, just filled out by different users weekly.

workbooks are named

user1.xls
user2.xls
user3.xls
etc...

all workbook names start with USER and are followed by a number .xls

There is a sheet in each workbook called TOTALS which calculates from other
sheets in the workbook

The TOTALS sheet in each book has a range of information from B2 to P27.

The Master.xls has an Identical TOTALS sheet and I would like cell B2 in the
master to be the sum of cell B2 from the TOTALS sheet from all USER
workbooks in the folder.

And Cell B3 to be the the sum of Cell B3 From the TOTALS sheet from all USER
workbooks in the folder.

The number of USER folders must be flexible as each week there may be a
different number of USER files in the folder.

all files, including the master file would be saved in c:tracking folder.

I am sure I have to do some For Statements and probably some while
statements, but have never worked with a non set number of files before.

Any help would be greatly appreciated.

Wally Steadman

retrieving data from a folder full of spreadsheets...

i create a customer's order in a spreadsheet and save the individual order.

i have a few hundred of these in an "orders" folder.

i would like to sort through these spreadsheets, retrieve a specfied customer and average their orders by item, etc.

???

thank you...!

Hi
I need an help to create a macro to copy the value from number of excel sheets and paste it into another single excel sheet.
I have number files which are located in one location (ex: test, test1, test2, test3, test4)
I need to open each file and copy the item number which is in Cell B2 in the file named test.xls and paste it in the FINAL workbook and then again copy Total_Liab value in the tab named Calculations in the same workbook test.xls (total liability value) which is the last cell value in the column Q (example: Q51 in the Calculations sheet) Q51 and paste it next to the same item number in the FINAL workbook. Once it perform the action it should close the test.xls worksheet and then open the next test1.xls worksheet and should follow the same procedure as explained for the test.xls. Could you please help to create an macro

Afternoon all,
Im quite new to OZgrid so this is one of my first problems

I have rows of information i have taken from PDF and placed into excel, i can't split the data by columns as the names of data all changes and have various spaces, though the information is given in the same format every time.

CONDUCTIVE FABRIC FLASH TO CHA 110144316 5 EA 0.05(point A*) 0.25 16-11-11

The detail in bold is what i need to pull.

I there a formula where i can choose to cut the data from a specified character (in this case " ")

then i would need to trim the data to point A (marked in example) then retrieve the next piece, trim again, retrieve and so on. Less you know of a faster way

To note, all numbers and characters and quantities of those may vary. the spaces between the data are consistant apart from the description which is far left.

My biggest thanks in advance!

I'll jump right into it:
We run a number of weekly reports that export to excel very nicely. My boss has asked for a workbook he can simply open and print that contains charts that show not only the weekly reports but a summary of all reports over the course of a calendar year.

The weekly report workbook calls data from 6 different exported documents to populate its charts. Can I somehow tell excel to look for workbooks with a specific name (in this case, the date range of the weekly report) and draw chart data from it if it exists, but to skip that file if it does not exist?

To put it another way:

Can I have a series of charts that will display weekly reports from all of January through June 14th at the end of this week AND will display data from January through October at the end of October as the new report files are saved?

The best solution I have come up with so far is to
A) Create each of the 52 weekly report file in advance and populate it with null data (zeros in all columns), then have our report generation program save over each of these files at the end of each week.

B) Build 52 weeks of data points onto each table in the final report workbook and have each chart call data from the dummy files. This data will be replaced with actual data each week, so each of these charts (line charts where X = Weekly progress and Y = numerical values) will show data up to the current week and no data for future weeks.

Am I reinventing the wheel here? Please let me know if I can give any more specific information on how I am setting this up.

Thanks in advance!

-Schwavan

I have a problem with some scripting I am hoping someone is able to help me with.

I have 2 workbooks, one is a master database which gets its data from the other workbook.

Data needs to be copied from a number of different cells and pasted into a single row in the master database.
I want to be able to do this when I click a button on the data sheet.

I want Cells - D9, E12, R18, K12, from the data sheet to be copied to the master database.

These need to go into the following order on each row;

Column A = R18
Column B = K12
Column E = D9
Column F = E12

I am able to get the data copied across from the data sheet to the master database only in one row.

I am using the following code:


	VB:
	
Dim wbM As Workbook ' where my data wants to go
 
Set wbD = Workbooks("data.xls") ' Data
Set wbM = Workbooks("master.xls") ' Master Database
 
With wbD.Worksheets("sheet 1") 
    .Range("D9").Copy 
    wbM.Worksheets("sheet 1").Range("E3").PasteSpecial Paste:=xlPasteAll 
    .Range("E12").Copy 
    wbM.Worksheets("sheet 1").Range("F3").PasteSpecial Paste:=xlPasteAll 
    .Range("K12").Copy 
    wbM.Worksheets("sheet 1").Range("B3").PasteSpecial Paste:=xlPasteAll 
    .Range("R18").Copy 
    wbM.Worksheets("sheet 1").Range("A3").PasteSpecial Paste:=xlPasteAll 
     
End With 
End Sub[/INDENT] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The problem is that the row in the master database that is being populated is written over when the script is run again!!....

I want to be able to continue on to the next field/row in the master database if it already has a record rather than writing over the existing record when the script is run again(by clicking on the button in the data sheet).

Is there anyway I can check to see if the row is populated on the master database and if it is to get the script to move onto next row before pasting the data it has copied from the data sheet?

I also would like to automatically open the Master database which is saved on a drive on a separate PC and close it after the data is copied across

At the moment both the data sheet and master database need to be open for the script to work.

Is it possible to get it to work with only the data sheet open?

Can anyone please help me with this problem?

Regi

I hope this will make sense
I have a huge spreadsheet.
The second column can contain one of say 10 diffirent funds (eg: "EB IF" or "EB NB" or "DLIF"). My first move is to sort this spreadsheet based on the column.

My previous procedure used a for loop that looked at all the values in the second column (from row 1 to the last row that contains data) one by one and if they matched a certain input (eg "EB IF") the whole row were pasted to another sheet. :
This is part of what I had


	VB:
	
 
Dim n As Integer 
n = 1 
 
Dim LastRow As Integer 
Sheets("UniverseDataMatrix").Activate 
LastRow = Range("A65536").End(xlUp).Row 
 
Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlGuess, _  OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _ 
DataOption1:=xlSortNormal 
 
For m = 2 To LastRow 
    Sheets("UniverseDataMatrix2").Activate 
     
    If Cells(m, 3) = Fund Then 
        Rows(m).Select 
        Selection.Copy 
        Sheets("Info").Select 
        Rows(2 + n).Select 
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _ 
        SkipBlanks :=False, Transpose:=False 
        n = n + 1 
    Next m 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This takes way too long too execute.
I want to paste the whole range at once (Eg: All the "EB IF" consecutive values). Thus I want the "m" forloop to find the first valued, count the number of consecutive values and based on that copy and paste the required range.

Please Help

Edit: WillR: Freddy, Please use the code tags when posting code. I have edited the above (2nd post of yours today). Reasons...
1. It saves me a job
2. It makes reading the code easier.
Please can you take the trouble to learn how to use the tags & use them in future. There is a TEST AREA for this purpose. Thankyou.

I have been searching the forums here and have found a few things but nothing that I have been able to use to do what I want exactly.

I have a file with several worksheets. 27 to be exact. A main sheet and then 26 others that are named A - Z.

Each A - Z sheet has 2 columns: A and B with a varying numbers of rows.

On the main sheet I have two fields that I each want to display the data from a random cell on one of the other sheets. The fields have to correspond so if the first field displays the data from A6 on sheet "F", the second field should display the data from B6 on sheet "F".

I don't know if this is possible but I feel that it is. One thing I considered is merging the A - Z sheets into one worksheet if that would be easier but if it can be done with the way I have it now I'd rather do it that way.

I am using a macro that pulls data from one sheet and displays the result in the subsequent sheets. My question is how you display the results after a certain number of rows, rather than after the last empty row. So right now i am using "d = Worksheets(a).Cells(Rows.Count, 1).End(xlUp).Row + 3" - the row + 3 part is pasting the next set of data 3 rows after the last row with data in it. The problem is each sheet has different number of rows of data so one sheet might have row 25 as the last empty row and another would have row 28 as the last empty row. So instead i would like to allot say 5 rows to each data set, and the next data is posted five rows after the first row with data.

so if sheet a has data on rows 25-26 and sheet b has data on 25-28, they will both paste the next data set on row 30 (5 rows after the first data was inserted).

Hi
i've got an excel work book, where the user enters the data in the format
Column Title1Column Title2Column Title3111222333
I've got formulae saved above all the columns right to the end of the sheet so that no matter how many columns there are the data will be crunched.
I'm very (very) new to writing macros so I'm looking for some help to make this more effiecent as the current way is very slow.
Is there a way of counting the number of columns and pasting the formulae across only the columns that contain data?
Thanks
Ed

Hello All,

Is anyone aware how to get around this problem or a code I can try? I want to do a copy and paste from a closed workbook into my open workbook. In my closed workbook, some of the cells I am pulling from sometimes contain over 1500 characters. In most of the codes I have tried, it cuts off after a certain number of characters; thereby not capturing all the data. Also, since these are large files that I am copying from, codes that are slow to execute can be a problem too. Below is my current code I am trying to work with (I have been trying different codes to try to get this to work). Thanks for taking the time to read.

Sub File_In_Local_Folder()
Application.ScreenUpdating = False
On Error Resume Next

'Call the macro GetRange
GetRange "C:Data", "Book1.xls", "master", "A1:g10", Sheets("copiedfrommaster").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

I routinely pull data from an increasing number of tabs in a worksheet. Is
there a way to pull the data from tabs as I add them without having to change
the formula for each added tab?

I would like to find a way to extract the same column of data from a large
number of Excel workbooks (formatted identically) and place the columns next
to one another on a single worksheet. How can I do this?

I copied a table of information from a page on the internet.

The information pasted into columns and rows, but the data does not work in
calculations or lookups.

When I look at a particular cell there appears to be spaces in front and
behind a piece of data. The data can be text or numbers. I tried a Find
including spaces, but the data was not found.

How do I convert data such as I described so it is usable?

Thanks

Hi all,

I've searched around and not quite found an answer to this question (or, if I have I haven't realised it because it's complicated!).

Earlier today I was given this bit of code for copying data and performing a simple calculation with it:
Sub copy_data()

'Copy data from "Data" to "Calculation"
Worksheets("Data").Range("A2:A" & Worksheets("Data").Range("A" &
Rows.Count).End(xlUp).Row).Copy Worksheets("Calculation").Range("A" & Rows.Count).End(xlUp).Offset(1,
0)

'Perform calculations
With Worksheets("Calculation")
    lrow = .Range("A" & Rows.Count).End(xlUp).Row

    For i = 2 To lrow
        .Range("B" & i).Value = .Range("A" & i).Value -
Worksheets("Data").Range("A2").Value
    Next i

End With

End Sub
This fixed my problem of copying data with a varying number of rows and has helped me immensely. I've now invented a new, similar problem.

Essentially, I will have two columns of Data (A and B). These columns will have a varying number of rows. In cells E2:E8 I will have some fixed values. I want to LOOKUP those values in column A and output the corresponding value in B into G2:G8. Is there a way to program a macro to do this, taking into account that the number of rows in A and B will vary? Something similar in style to the code above?

Thanks in advance for any help you can give.

Regards,
Michael

I have what I think is a simple problem, but I have not been able to find a satisfactory solution in the forum yet.

I have a workbook with several worksheets, one worksheet is called Monthly Data. I need to copy all the data from Monthly Data beginning with row 2 to another Worksheet called YTD Data, beginning after the last filled row. Monthly Data has a variable number of Rows and Columns. There is a header in Row 1 with a title for each column.

Doesn't seem too complicated but have found no solution that fits this scenario exactly. Any help would be greatly appreciated.

John

Ok, I've been using Excel for many years but I'm not considered an expert by any stretch ...

This problem has me stumped and maybe there is no simple solution...but I'm hoping there is

I have dollar amounts that I need to distribute amongst varying numbers of columns and not have have the total distributed be over or under the original amount by any number of cents. When I simply divide the dollar amount by the number of columns, the total of those columns can sometimes be more or less than the original dollar amount by a few cents.
e.g.
Dollar    # of       A        B         C         All Columns
Amount    Columns                                 Total
--------------------------------------------------------------------
$25.05     2        $12.53    $12.53              $25.06
$11.47     3        $ 3.82    $ 3.82    $ 3.82    $11.46
$25.05 divided into 2 columns gives $12.53 in Column A and $12.53 in Column B. Total of Columns A and B is $25.06. Over by a penny.

$11.47 divided into 3 columns gives $3.82 in Columns A through C. Total of Columns A through C is $41.46. Under by a penny.

I know I could simply always add or take away the pennies from one column, but I would prefer the process to be random or formulated in such a way that the Column to which the extra pennies are added to or taken away from differs in order to be "fair to each column".

Man, I hope I explained that properly.

Thanks for ANY help with this.

In Excel how can I sum the same cell on a VARYING number of sheets with
different names ? Is there a wildcard function or sum the same cell from ALL
worksheets function ?

I am summarizing a .csv export from a survey program, and part of the survey is an open-ended comment. I want to be able to use the same template, and just copy in the data from a new survey to update. However, one survey might have 3 respondents, and one might have 6. My current formula is "=CONCATENATE(C1,CHAR(10),C2,CHAR(10),C3)" where C1:C3 are the comments (and CHAR(10) gives me a line break). Is there a way to capture a variety of respondents (up to 15) without using huge string of cells, and without having additional lines if there are only a few respondents?

Dan

I'm in need of a macro that takes data from cells in column A and moves them into different cells. Each block of data includes a company name, contact name, address and usually (but not always) phone, fax, email, website, etc. I want to move each set of contact info into a set of cells that begin in column A, starting with company name.

Please refer to the attached spreadsheet and thank you very much for your help.

Hope you guys can help - l need to consolidate up to 3 columns of data from a varied number of worksheets within the same workbook. The sheets are (format/layout) structured in the same way. The columns need to get added next to each other in the consolidation sheet.

Hi all,

I've been searching previous posts but haven't found a solution to my problem.

I'm putting together a workbook that will list a variable number of 'issues' each with its own worksheet and reference number. I have a bit of code that will copy the worksheet and rename it based on the number of worksheets in the workbook and therefore generating the reference number. Each issue (worksheet) will have a significance number associated with it generated within the worksheet based on user input.

What i would like to do next, and this is where i'm struggling, is to use the worksheet change event to populate a range of cells with the reference number, description and significance value so that i can then plot a graph and sort them in order of significance. This will be in an additional worksheet within the workbook. However, as you can imagine i don't want duplicated reference numbers and the range in question may change as new worksheets/issues are added.

Any pointers would be greatly appreciated.

Cheers,

Matt

Hi all, I need a function to show the number of consecutive duplicates in the same row. If column "A" reads
1
1
1
3
1
1
... the generated column "B" should read
*
*
3
1
*
2
... any ideas?
your help is greatly appreciated!

I am trying to lookup data from two concatenated colums and retrieve data
from a pivot table where the column & row matches the concatenation, return
the max value in the pivot table. Below is my example data:

Pivot Table
Max of Value
Column Row Total
AB_N AB
AL 1.25
AR 1.15
AZ 1.62
BC
CA 1.25
CO 1.95
AL_N AB 1.95
AL 3
AR 1.75
AZ 1.65
AL_S AB 1.95
AL 3
AR 1.75
AZ 1.65

I want to retreive the max value where the concatenation of the below table
matches matches the column and row in the below table.

Table
O Region D State Concatenation
AR_N FL AR_NFL
PA_E GA PA_EGA
VA_E WA VA_EWA
VA_E TX VA_ETX
VA_E FL VA_EFL

Can anyone help?


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