How To Transpose Large Dataset With Many Worksheets

Apr 3, 2013

I have a problem and I can't figure out how to do this, I have tried using both macros and functions (INDEX for example). The problem is as follows, I have a dataset of 27 worksheets, each worksheet has between 30k and 60k of rows and 25 columns. They are set up as follows:

------------------1990 1991 1992 etc.

Firm_A-Variable_1
Firm_A-Variable_2
|
|
|
Firm_B-Variable_1
Firm_B-Variable_2
|
|
|
etc.

Now what I am looking for is an easy way to transpose the data, I would like to have it looked as follows:

-------------Variable_1 Variable_2 Variable_3
Firm_A 1990
Firm_A 1991
Firm_A 1992
|
|
|- ---- 2012
Firm_B 1990
Firm_B 1991
Firm_B 1992
|
|
| ------2012
etc.

It is basically impossible to do this by hand, each of the 27 worksheet has between the 3000 and 6000 firms and each firm has 57 variables (these are identical for all firms). Also the the firm names and the variable names are in the same column, these should be seperated as well (they are connected with a hyphen).

View 1 Replies


ADVERTISEMENT

Using PRODUCT For Large Dataset

Jan 6, 2014

I have a set of 5,800+ data points between 0 and 1 that I would like to multiply together. When I use PRODUCT for the whole set, the formula returns 0. However, I can use a smaller subset of the data to return a very small number. I'm curious if Excel has a closest-number-to-0 or number-of-cells-for-PRODUCT limitation. Is there another way to perform this calculation?

View 4 Replies View Related

Pulling Nth Date From Large Dataset?

Mar 29, 2013

we use # of days per disbursement as a performance measure to ensure that we are providing out grantees with the appropriate amount of service. I keep a tracking chart that I manage with overseas partners that use these dates to prioritize the 30+ grantees in their portfolio at any given time. It would be great if this # of days to disbursement #1, disbursement #2, etc could automatically pull to show them who they have neglected.

"Sheet 1" = Overview sheet to see general information (where I'm trying to pull to)

"Sheet 2" = table to track information as the disbursements or other actions are processed per grant

Column A (on both sheets) gives the grant reference

Column B (data entry sheet) gives the date the payment was sent

When I do =SMALL(('Sheet2'!B:B),2), I get the 2nd smallest in the whole sheet, but then when I try to make an IF function to tie it to the specific grant...

=IF('Sheet2'!A:A,A2,SMALL(('Sheet2'!B:B),2)) --> this gives me a 1905 date

I've tried a bunch of different formulas and tried reformatting the dates... but I'm having very little success...

View 12 Replies View Related

Calculate Variance Across Large Dataset?

Nov 25, 2013

I am looking to calculate variance across a large data set and would like to know if a macro is possible to calculate for a specific unique cell ID. East, Central, or West and calculate variance across that region.

For instance, in my data set if I have something similar to below. How would I calculate variance in the different regions? Is it possible to automate this process? Also could the Analysis ToolPAk be used instead or in conjunction?

OrderDate
Region
Rep
Item

[Code]....

View 4 Replies View Related

SUBSTITUTE With Conditions - Large Dataset

Jun 16, 2014

I have the following structure in values found within a large dataset

6A-6200 A
6A-6156-A

I used =SUBSTITUTE(CELL," ","") to remove the space in the first value: Works fine

I am trying to remove the 2nd occurence of the - in the second value: Desired Result 6A-6156A

And how I may be able to complete both within one formula.

View 3 Replies View Related

Pulling Info From Large Dataset Using Formulas

Jul 12, 2014

I am accustomed to using filters to find a lot of my information in large datasets.

However, now I am trying to use formulas to return specific values. For simplicity's sake, I have included a sample below with a couple types of scenarios I am looking to solve through the use of formulas. Would this involve sub-arrays perhaps?

sabinfire1.xlsx
Excel questions.docx

View 5 Replies View Related

Creating Specific List From Large Dataset?

Jan 2, 2014

We collect loan payments for 36 months from customers.

Column A lists 1000+ customers.

Column J lists the date we received payment 1 ... Column Q lists the amount we received on payment 1.

Column R lists the date we received payment 2 ... Column Y lists the amount we received on payment 2.

Column Z lists the date we received payment 3 ... Column AG lists the amount we received on payment 3.

This repeats for all 36 payments.

New customers are loaded in each month, so be aware that Column J, Column R, Column Z (and so on) have dates from 2011 and 2012 and 2013.

We'd like to create a list of all customers that have not made a payment for the current month as of a certain day (say the 12th). So this month, on January 12th, we'd like to search our data for all customers that don't have a payment listed between January 1st - January 12th.

View 14 Replies View Related

Large Dataset - COUNTIFS On Blocks Of Text

Nov 3, 2011

I have a large dataset. under the heading Theme there are blocks of text, ie not single words. i have created a simplified version below

Sample table:

StateThemeVictoriatradeNew South Walestrade, ownershipNew South WalestradeVictoriaownership,
test textVictoriatest text, tradeVictoriatrade, ownershipVictoriaownership, trade

I want to count, for example the number of times the words 'ownership' OR 'trade' appear for Victoria.

The formula i am using is doubling up on the last 2 entries.

This is what I have:

=SUM(COUNTIFS(A2:A8,{"Victoria"},B2:B8, {"*ownership*";"*trade*"}))

View 9 Replies View Related

Macro To Filter Out Information In Large Dataset?

Feb 9, 2012

I have a large itemised call bill that i need to do some regular analysis on and wondered if I could automate most of it.

In column C is a list of mobile numbers, in column F the numbers they called (this is an itemised bill so each line represents one call, meaning each number has multiple rows) finally in column K is the cost of each call.

I want the macro to look through column F (number called) and if there are less than 5 instances of that number that are under 0.30 each in cost to be deleted.

Example: if in column F the number 07500 100100 appeared once with a cost of 0.29 I want it deleted but if it appears 6 times with an accumulated cost of 3.50 i.e. more than 0.30 per call averaged out, then i want it to remain on the sheet

View 3 Replies View Related

Overlay Two Bar Charts Together / Large Dataset For X Axis

Feb 19, 2013

I have the following two bar charts. (see links below). I would like to overlay both these bar charts together and obtain the chart shown in link 3.

For example, at 4.4 GHz and 1.8m antenna, two values (downtime/year) are possible 15 min or 557min. This is represented in the third figure Since the first chart contains small values and the second chart contains large values for the x-axis, will I able to change this to log scale for ease of analysis?

[URL]

View 1 Replies View Related

Overflow Error While Looping Through Large Dataset?

Jan 29, 2014

The code below works fine on smaller data sets. I tried it on a data set of over 165000 records and it gives me the error:

"Runtime Error '6': Overflow"

Clicking debug highlights this line:

rowCount = .DataBodyRange.rows.Count

Code:
Sub Fixtable()
Dim lo As Excel.ListObject
Dim loRow As Excel.ListRow

[Code]....

View 4 Replies View Related

Excel Formula Retrieving Data From Very Large Dataset

Jan 17, 2013

I've been unsuccessful in trying to write a formula that retrieves a single result based on two criteria (from a large set of data on a separate worksheet). I've tried various INDEX MATCH combinations but no luck.

A
B
C
D
E

1
DATE
TEAM
PITCHERS
RESULT

2
4/1
nyy
Sabathia
???????

[Code] ........

So this is a very simplified version of my real data set which is about 20 times this size. The first worksheet is where I want to store my retrieved results (lets say D2 for example). I want to retrieve data from the second worksheet that matches two criteria (exactly) originating from my first worksheet. The two criteria to be matched from the first worksheet are, for example, A1 (sabathia) and F2 (the date 4/8). The complicated part is the desired result should be from the corresponding K/9 column in the second sheet, which in this case (based on sabathia and 4/8 criteria) is I2 (result would be 3). It's complicated since I can't just tell the formula to look down a specific K/9 column, I need to search ALL the K/9 columns in the sheet (of which there are many). Is this even possible with some sort of nested INDEX MATCH? Any possibilities outside of VBA programming, or is that the only way?

View 8 Replies View Related

Creating Frequency Distribution Chart From Large Dataset - VBA?

Dec 16, 2013

I have a table that can at any point have from a couple hundred up to a couple thousand rows. Within this table lies a column entitled " Offer ". I want to plot the figures in the offer column as a frequency distribution chart.

I plan to do this by listing the x-values (Offer figures), and then using count if formulas to calculate the frequency of that x-value. Then using a simple clustsered column chart to create the visualisation of the frequency distribution.

My question is.... in my large data set, is there any way to get VBA to insert a list of the range of figures in the Offer Column, ? I can figure out how to copy down the countif formula to populate the corresponding frequency column, but how can I have some VBA to dynamically adjust my x-values (offer figures)?

For example... say in the first data set I have

Offer, Frequency
1 10
2 20
3 25
4 20
5 15

that's fine if I make the chart, but what if the data set changes, I want VBA to give me a list of all the offer values, and then I can write some code to insert and copy down along the frequency column the countif formula.

The ultimate goal is to have a frequency chart that will be synced to the self-updating dataset.

View 1 Replies View Related

Pivot Table To Count Unique Items In Large Dataset

Oct 15, 2012

I'm trying to count how many production orders i have per week. However, there are duplicated production orders per week. I only want to count how many unique orders there are for each week. I only see the ability to "Count", which counts my duplicates as well so it over inflates my true quantity.

View 3 Replies View Related

Transpose Large Data In Proper Format?

Apr 11, 2014

I have huge data

I Want data to be transpose in a below format:-

Emp Code Emp Name Intime Outtime Total worked minutes Extra worked minutes Attendance Shift


And also add the row as per the number of days worked.

VBA code to transpose large number of data.

View 3 Replies View Related

Consolidate & Transpose Worksheets

Jan 3, 2008

I have several worksheets in one workbook. To consolidate, I am copying the information from the worksheets into one summary sheet. The worksheet information is entered vertically:

Charges 2007 2006
Inpatient 30000 25000
Outpatient 32000 21000

I want the information in the columns to be transposed on the summary sheet, but still linked to the original worksheet:
2007 Inpatient Outpatient 2006 Inpatient Outpatient
30000 32000 25000 21000

Copy and paste special with transpose does not link the spreadsheet. I tried to drag across after filling one cell, but the fill function increases by column from left to right, not row number. (It increases row number, which is what I need, only dragging down.)

View 3 Replies View Related

How To Transpose Data Across Worksheets Into One Analysis Sheet

Oct 20, 2013

I've used the following formula successfully to pull data from multiple sheets and provide the total on an analysis worksheet. Example of worksheets and formula.

=DSUM(INDIRECT("'"&A2&"'!D:D"),$B$1,INDIRECT("'"&A2&"'!D:D"))

Example of one of the individual sheets holding original data:
A
B
C
D

[Code].....

Example of what I want to see using the Transpose formula (Analysis sheet):

=TRANSPOSE(INDIRECT("'"&A2&"'!C:C"),$C$1,INDIRECT("'"&A2&"'!C:C")) (My theory that currently doesn't work. I'd like to be able to pull data across worksheets and yield the transposed data into the corresponding row of the same merchant row.

A
B
C
D

[Code].....

View 9 Replies View Related

Update Large Amount Of Worksheets

Jun 19, 2009

I have a large amount of worksheets that require a formula update. Update itself is simple. Each formula in the cell has to be devided by different cell. My problem is that I have a big number of worksheets to do this in and they are in different workbooks. Is there a faster way of doing it besides manually updating each cell?

View 9 Replies View Related

Large Function For Multiple Worksheets

May 6, 2007

I am trying to use the 'large' function to input data from multiple spreadsheets, but am uncertain how to do so other than to use the same rows/columns in each and to use only continuous worksheets (that is, I want to enter data from worksheet 1,3,5 and different columns in each, for example).

View 4 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

Copy Column And Transpose To Row - Multiple Worksheets To Summary Sheet

Jul 21, 2014

I have over 200 worksheets - separate participants data. On each sheet there is a summary column of data at the moment. I now want those columns of data copied to a summary sheet but transposed to rows.

I have attached an example with 3 worksheets and the sort of summary sheet I am after.

View 6 Replies View Related

Macro To Link Large Number Of Cells Between Worksheets

Feb 18, 2008

I would like to write a macro that links cells in one workbook (working) to cells in another workbook (summary).

I don't want this to be a straight swap thought, I'd like it to contain the following formula:

=IF(Working!G8="a","a","")

The cells in the working workbook are listed vertically, while the cells in the summary workbook are listed horizontally.

ie. link G8-G18 in working to B3-K3 in summary
link H8-H18 in working to B4-K4 in summary
...etc

View 9 Replies View Related

Splitting Large Table Into Separate Worksheets Keeping Format Same

Apr 26, 2013

I have 2 large XLS sheets that need to be split into seperate sheets.

The first is only 5 columns wide but the amount of rows changes day to day.

The second is a maximum of 7 columns wide and again the amount of rows will change.

I'm hoping for a VBA code to be able to do both on seperate books.

The A Column has the name of the company, and this can include (/,&) that will need to be removed, the names can also exceed 31 characters.
I would like to seperate the sheets by the company name and have the name appear as the sheet name.

Also the formatting from the master sheet to be copied to the resulting sheets, with a header row.

I have included the data for the larger of the two workbooks. data.jpg I have to pull the smaller report each day and the larger report each week.

View 3 Replies View Related

Copy And Paste Portions Of Excel Worksheets - Picture Is Too Large

Dec 2, 2013

Recently, when trying to copy and paste portions of excel worksheets, I've been getting the message: The picture is too large and will be truncated.

I have copied and pasted this same way for years and the problem has just arisen over the past month. An excel issue or possibly something else and just getting the message with Excel since it's the software I'm using 90% of every day?

My files are stored on a network drive, not my hard drive.

View 2 Replies View Related

Simplifying Formula When Data Needs To Be Extracted From Large Number Of Worksheets?

Jun 16, 2014

simplifying a formula which gathers data from about 50 worksheets from within the same work book.

The data to be gathered is in the same cell on each worksheet and is simply a number but i want the SUM of theses numbers carried forward to another worksheet. Each worksheet is named by date i.e. sheet 1 is named "16 June 2014" and sheet 2 is named "23 June 2014" and so on until "30 March 2015" (Each sheet represents one full week Monday - Sunday).

View 5 Replies View Related

Expanding Dataset In One Column By Inserting Dataset From Another Column

Feb 28, 2014

I am having trouble finding an efficient way to expand a set of data that I have by adding another column to it.

Look at attachment : help.xlsx‎

I can do it manually but I have 5000+ rows of data that I need to selectively expand do accommodate the data from the new data set.

View 4 Replies View Related

Large CSV File: Too Large To Open. Split

Aug 6, 2003

I've got a 80 Mb CSV file and would like to open and work with it. Too many lines (90000 or so).

Is there a way to split ( ) this file so I can open two files instead?

View 9 Replies View Related

How To Add VLookup To Dataset

Dec 8, 2013

Trying to add a vlookup to a data set and cannot remember how to do.....

I have a table with State and Suburbs. I want to look at both these within another sheet to ensure the post code is correct......

In the attached I want to show in Sheet1 Column D the post code from Sheet2. As there may be suburbs with the same name within Sheet1 I need to ensure the correct Postcode for the suburb and the state.

(Checking manually entered data....)

Exceltest.xlsx

View 3 Replies View Related

Lookup Value Within Matching Dataset

May 14, 2014

I have a grid that I need to lookup and return the project hours for each system from the data set. The grid has the projects listed in the rows and the systems listed across the columns. The data set has a list of each projects systems hours. I am having problems with the formula when the data set has multiple records (multiple systems) for each project. It seems very simple, but I just can't get my arms around it. Please see the attached for example.

View 7 Replies View Related

Transposing Data...again In Another Dataset

Dec 19, 2009

I was recently helped very much by the forum moderator and code from D_Rennie in a file to transpose about 25,000 rows into a few organized columns. The code worked very well. I have a slightly new twist on the problem. I have uploaded a different file with a different data structure in the columns but the same need for the data to be transposed to the columns under the same headings. This VBA worked very well before, but I can't seem to re-use it for this new dataset.

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved