Free Microsoft Excel 2013 Quick Reference

Consolidating Spreadsheet

Good Morning,

I have a spreadsheet with roughly 3000 transactions. I would like to consolidate the spreadsheet so that each security only appears once and that the values in rows B-E are summed.

I have attached an example and if anyone could help me, it would be much appreciated!

Thanks.


Post your answer or comment

comments powered by Disqus
In my sample file http://www.srands.co.uk/exoftable5.xls 300kb

2 Spreadsheets RESULTS P 206 and RESULTS H CDTI,have identical headings, all non-blank rows need merging / consolidating within the prepared 4th spreadsheet 'MULTI-RESULTS'.

The data in the 2 spreadsheets will change, depending on the information available.

However the consolidating feature of excel, excludes text/word entries (So sayes MicroSoft), the vba codes I've tried don't work, and the RDB solution isn't what I need.

I would like to consolidate multiple spreadsheets within a specific folder on
a server to a single sheet on a spreadsheet located on my (or someone else's)
PC. The spreadsheets on the server are all in the same format, in that they
have identical columns as does the consolidation spreadsheet held locally.
Ideally I'd like the code to write to the next available line of the
consolidation spreadsheet. Thus.

Server

Spreadsheet1 - 3 rows of data
Spreadsheet2 - 4 rows of data
Spreadsheet3 - 6 rows of data
etc
etc

Local PC

Consolidation Spreadsheet - First row includes Headers.

So the Consolidation spreadsheet would take data from Spreadsheet1 and place
it on row 2,3,4 then place the data from Spreadsheet2 on row 5,6,7,8 etc.

Many thanks in advance for any help you can provide.

Hello,

I am trying to consolidate 3 spreadsheets into 1. Each spreadsheet is in
table format.

The first spreadsheet is 'Asia, the 2nd 'Europe', the 3rd 'Americas'. I
would like the consolidated 4th spreadsheet to be a summary or an all
encompassing global view. Columns are the same.

Question - How can I create a function which brings in all Asia,Europe and
Americas information into one table format without previous knowledge of the
row count in each. The rows will be a variable in each spreadsheet.

Any help would be much appreciated.

Thanks
Acase

This is my first post, so I am sorry if this has already been covered, but will ask anyway. Maybe someone that lurks here frequently can point me in the right direction.

I have two spreadsheets. Each spreadsheet has roughly 65% overlap (same names)with the names of companies.

In addition, each spreadsheet has about 17 columns with 10 or 12 columns being the same data, i.e. company name, city, state, phone etc...

However, each spreadsheet also has several other columns that are unique to each.

I would like to create on MASTER spreadsheet which combines both companies and columns of information from both spreadsheets.

Can excel do this? Or, need I purchase an add-on program like "Consolidator" which I have seen advertised on the web?

In my sample file http://www.srands.co.uk/exoftable5.xls

2 spreadsheets (RESULTS P 206 and RESULTS H CDTI) with identical headings, non-blank rows need merging/consolidating within the prepared 4th spreadsheet) 'MULTI-RESULTS'.

The data in the 2 spreadsheets will change, depending on the information available.

However the consolidating feature of excel, excludes text/word entries (So sayes MicroSoft), the vba codes I've tried don't work and the RDB solution isn't what I need.

Crossthread(s):
http://www.mrexcel.com/forum/showthr...00#post3137900

Easy question for you experts. But I can't find the answer and it bugs the hell out of me. It's difficult to even word for a search engine.

I've been consolidating spreadsheets at work from other employees, historical files, etc for a project.

Some individual sheets stop in normal view after the last line of data. The toolbars conveniently slide down or across to the end of the data. I like this.

Others sheets go on for 256 Columns and 65536 Rows making it difficult to quickly find the end of the data.

How can I "crop" or delete the cells that aren't needed. I don't want to set the print area. I only want to view the info on the screen.

Thanks in advance.

I have a basic understanding of VBA and am trying to create a macro that will consolidate all worksheets within a workbook into one tab.

I have the found the following code which consolidates all the data but I need it to also copy the name of the worksheet alongside the data from that sheet - can anyone help?

Sub mergeallsheets2one()
Sheets(1).Activate
lastrow = ActiveSheet.UsedRange.Rows.Count
For Each Sheet In Sheets
If Sheet.Index 1 Then
RowCount = Sheet.UsedRange.Rows.Count
Sheet.UsedRange.Copy Destination:=Sheets(1).Cells(lastrow + 1, 1)
lastrow = lastrow + RowCount
End If
Next Sheet
End Sub

Thanks in advance!

Hi everybody, new here and in a lot of bother!!

I am doing budget work. I have created a consolidated sheet which links to 5 draft sheets which each represent a division of our company.
Each draft has a name and I update the draft everytime I make a change ex: Nationwide v1d1, say I then change a few figures I then save this as Nationwide v1d2 and so on.

My problem is that I want the consolidated spreadsheet to automatically pick up the latest draft each time so the consolidated sheet is always up to date. Sometimes it does pick up the latest drafts and sometimes it doesn't. I know there must be a logic to this but I can't figure it out.

Help!!!!!

I have two seperate worksheets with data from two companies. Example:

(Worksheet 1)

COMPANY A
McDonalds Account
Burger King Account
Wal Mart Account

(Worksheet 2)

COMPANY B
Outback Account
Home Depot Account

I would like to create a third worksheet that will consolidate the
entries for the two companies. I would want it to look like:

BOTH COMPANIES
McDonalds Account
Burger King Account
Wal Mart Account
Outback Account
Home Depot Account

The problem is that the number of entries in each company will change
from month to month. But I would like to create a formula of some sort
that will list all entries in the first sheet and then pick up showing
the entries in the second sheet in one consolidated list. I've been
going this route:

=IF('SHEET1'!A2"",'SHEET1'!A2,'SHEET2'!$A$2)

This gets all the first list and the first entry of the second list but
I can't modify the formula to add entries in order after the first entry
on the second sheet. I could manually modify the formula to display the
data right this month but when the variable change next month it will
need to be modified again (I could copy paste alot faster if I was
going to do this).

Is there anyway to do this without getting into VBA code?

Thanks!
Jon

--
jhicker
------------------------------------------------------------------------
jhicker's Profile: http://www.excelforum.com/member.php...o&userid=31736
View this thread: http://www.excelforum.com/showthread...hreadid=514516

This app has been working well for over a year and suddenly the links or
pathways have change and I don't know how to correct the errors also formulas
in the destination spreadsheet are changed.

I have two seperate worksheets with data from two companies. Example:

(Worksheet 1)

Company A
McDonalds Account
Burger King Account
Wal Mart Account

(Worksheet 2)

Company B
Outback Account
Home Depot Account

I would like to create a third worksheet that will consolidate the entries for the two companies. I would want it to look like:

Both Companies
McDonalds Account
Burger King Account
Wal Mart Account
Outback Account
Home Depot Account

The problem is that the number of entries in each company will change from month to month. But I would like to create a formula of some sort that will list all entries in the first sheet and then pick up showing the entries in the second sheet in one consolidated list. I've been going this route:

=IF('SHEET1'!A2<>"",'SHEET1'!A2,'SHEET2'!$A$2)

This gets all the first list and the first entry of the second list but I can't modify the formula to add entries in order after the first entry on the second sheet. I could manually modify the formula to display the data right this month but when the variable change next month it will need to be modified again (I could copy paste alot faster if I was going to do this).

Is there anyway to do this without getting into VBA code?

Thanks!
Jon

I hope you may be able to assist me on an issue with consolidating mismatched data from two spreadsheets. Both spreadsheets share the same UNIQUE ID; however, one contains multiple instances of the same UNIQUE ID because it has multiple milestone. I thought Consolidating Worksheets By Category would work but it seems that it does not work with text.

Example

Spreadsheet 1

Column 1 Column 2
Unique ID 1 Milestone 1
Unique ID 2 Milestone 1
Unique ID 2 Milestone 2
Unique ID 2 Milestone 3
Unique ID 3 Milestone 1
Unique ID 3 Milestone 2

Spreadsheet 2

Column 1 Column 2
Unique ID 1 Issue 1
Unique ID 2 Issue 1
Unique ID 3 Issue 1
Unique ID 3 Issue 2

GOAL: CONSOLIDATED SPREADSHEET

Column 1 Column 2 Column 3 or Column 4
Unique ID 1 Milestone 1 Issue 1
Unique ID 2 Milestone 1 Issue 1
Unique ID 2 Milestone 2
Unique ID 2 Milestone 3
Unique ID 3 Milestone 1 Issue 1
Unique ID 3 Milestone 2 Issue 2

The data is laid out differently in the source worksheets or the same source worksheet is missing rows or columns."

Is there a macro and a non macro way to approach this?.

Hi. Was wondering if anyone knew if there was an easier way to consolidate a large number of spreadsheets into one without doing a load of vlookups (due to them being large sheets and other people colleges adding new lines)?

For example are there any macros that can pick up a load of data from the other spreadsheets and past them into my consolidated spreadsheet without any hassle. Then perhaps have formulas to pick up the data from the consolidated workbook.

Hi, first, a quick thanks for all who've contributed to this forum. I've used it many times in the past and I hope to pass back some of that help.

In the meantime, I have a question I can't seem to find an answer for here or elsewhere. I have a spreadsheet with two columns. The first is a list of names that are all unique, but in that list are several that are very similar.

To illustrate this problem, visualize a list of software listed with versions and a count of licenses:

(sorry, not sure how to type in a spreadsheet. I'm trying to show row number and columns A and B separated by a | )

A | B
1 Excel 4.3.2.1 | 3
2 Excel 4.3.2.0 | 7
3 Excel 4.2.1.1 | 10
4 Powerpoint 4.2.3.4.0 | 8
5 Powerpoint 4.2.3.4.1 | 3
6 Powerpoint 4.2.4.5.1 | 1
7 Word 1.3.2 | 3
8 Word 1.3.0 | 11
9 Adobe Reader 4.2.5 | 7
10 Java 8.23 | 15
ZZZZZZ

Is there a way to compare each cell to the one below and add the count if the two cells are similar?

The following code works pretty well:


	VB:
	
 
 ' set the initial values
 ' get the total row count
x = Cells(Rows.Count, 1).End(xlUp).Row 
 
 ' y will track the rows on the second spreadsheet.
y = 1 
 
 ' get the value of the first cell
subtot = Cells(2, 2) 
 
 ' Count each finding by comparing the finding name in one cell to the finding name in the next cell
 ' if the first five characters are the same, consider them a match
 ' Then add the counts together.  If they aren't get the subtotal and paste the finding name and total count
 ' on the consolidation spreadsheet
 
For w = 2 To x 
     
     ' the last row has a bunch of ZZZZ's so if this is a match, stop the  routine
    If Left(Cells(w, 1), 6) = "ZZZZZZ" Then Exit Sub 
    comp = Left((Cells(w, 1)), 6) 
    Comp1 = Left((Cells(w + 1, 1)), 6) 
    If comp  Comp1 Then 
        Sheets("Consolidation").Cells(y, 1) = Left((Cells(w, 1)), 6) 
        Sheets("Consolidation").Cells(y, 2) = subtot 
        subtot = Cells(w + 1, 3) 
        y = y + 1 
    Else 
        subtot = subtot + Cells(w + 1, 3) 
    End If 
     
Next w 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The above code yields the following results:

Excel 20
Powerp 12
Word 1 14
Adobe 7
Java 8 15

As you can see, by comparing the first x characters in each cell I can get a pretty good total for each software. However, I don't get a good names (Powerp, Word 1, Java 8) and picking how many LEFT characters to compare is tricky. Too many and you don't get enough matches and too few and you get too many matches.

So my question is this: is there a way to compare the entire cells and determine a percentile match? That is, if the cells are 90% similar then add the counts?

(BTW, the ZZZZZZ is there for other reasons not pertinent to this question)

Thanks.

Hi,

I have a excell spreadsheet that consolidate information from several other excel spreadsheets generated daily. To make this consolidation I have to open each daily excell file and copy some columns(always same ones) to my consolidate spreadsheet, but each daily information goes into a different column in the consolidated file.
So, I need a way to convert the columns names into numbers to be able to use loops on the VBA code where I can specify in each column the paste has to be done.

Anyone has the VBA code that can make this column name conversion into numbers?
Thks

Having moved jobs and now using an older version of Excel ( 2000 SP3 ) I find myself struggling to delete redundant file links in my consolidation spreadsheets.

Can this be done in Excel 2000 ?

Thanks

We have a 260+ line 8 column worksheet containing text.

Text has been obtained from other spreadsheets forwarded by other sources.

We cleared formatting, and pasted the content into our "consolidated"
spreadsheet

We want to do an alpha sort on Column A

Data -> sort

But the sorrt ends up providing 2 A-Z alpha lists, instead of 1 full alpha
A-z list.

any ideas on how to correct this?

I have thousands of links to many consolidated spreadsheets created in Excel
2003. Now that I use Excel 2007, how do I convert the links without breaking
them.

We have a 260+ line 8 column worksheet containing text.

Text has been obtained from other spreadsheets forwarded by other sources.

We cleared formatting, and pasted the content into our "consolidated"
spreadsheet

We want to do an alpha sort on Column A

Data -> sort

But the sorrt ends up providing 2 A-Z alpha lists, instead of 1 full alpha
A-z list.

any ideas on how to correct this?

Hi,

I need to create a reference to an external worksheet (which is not necessarily active) that could be used by other cells as in the "indirect" function, but refering to external data.

My objective is to define the path of a certain file in a cell (like C:Documents and SettingsMy DocumentsForecastsFile X), and define different ranges of such spreadsheet in other cells. The reason for that, is the need to use the exact same structure in several different consolidating spreadsheets that I have to create. I would have different consolidating spreadsheet, which would collect the same type of information, in the same format, every month, but from different sources. Every month, the name of the spreadhseets would change, so my only work would be defining the new name in one cell and all my links would all be automatically updated.

Does anybody now a relatively easy way to solve it?

Thanks,
Alex

I am having problems with the macros I set-up in my spreadsheet. It's not working the way I wanted it to work.

Here's the scenario: I have several excel spreadsheets extracted from my Software and these consists of 8 entities which I wanted to consolidate in one excel file. All sheets, including the consolidated spreadsheet (master file) have the same reference cells.

What I wanted to happen is to lessen the time for consolidation. I wanted to have the files I extracted from the software to go directly to its specified folder, all the while at the same time, populating my master spreadsheet of the date from extracted files. The figures should be consolidated in column B. It's actually two columns master files - 1st column for the description and the 2nd column for the amount.

Below is the macro I set:

Option Explicit

Sub ImportConsolidateData()

' Consolidate Macro
' Import the first sheet from files in a folder
'WCC Databaseper entity
Dim strFileName As String, sName As String
Dim MstWb As Workbook, StrWb As Workbook
Dim NR As Long, LR As Long

Set MstWb = ThisWorkbook
ChDir "C:UsersDesireeDesktopWCC Database"
strFileName = Dir("Conso*.xls")

'Import data from found files
Do While Len(strFileName) > 0
'Open book
Set StrWb = Workbooks.Open(strFileName)
'Grab rows and turn on consolidate
Range("B17:B146").Copy
Range("B" & Rows.Count).Consolidate
'Put data in Master workbook
MstWb.Activate
Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll
NR = Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
LR = Range("B" & Rows.Count).End(xlUp).Row
Range(Cells(NR, "A"), Cells(LR, "A")) = sName
'Get next file to open
strFileName = Dir
'Close current store file
StrWb.Close False
Loop

End Sub

I don't know where the error lies.

I have a macro that takes values from spreadsheets sent to our office and
posts the values to a consolidated spreadsheet.
I need a value that is the result of a sum function (would look like this if
in a cell ***(c106/c92). I have to perform the function and then assign it to
a variable named iValueCMGM. The value of this variable is then pasted into
the consolidated workbook.
I tried this but it didn't work:
iValueCMGM = .Range("C106" / "C92")

Can anyone help?
Thanks!

Hello,

For a project at work, I have a folder full of identicle documents. On the page "Manager Summary" is a 5 row table I need to copy into a consoidated list. That consolidated list needs each table from each document in a long table. Essentally it is copying every table and pasting them one underneith another in a consolidated spreadsheet.

In my consolidation form, I have the following code:

Sub ReadFolder()
'
' ReadFolder Macro
'
Dim fPath As String, fName As String
Dim RowNum As Integer
RowNum = 9
Dim src As Workbook
Set src = ActiveWorkbook
Dim dst As Workbook
Application.DisplayAlerts = False
Application.ScreenUpdating = False
fPath = "C:Users*******DesktopIT Capability AssessmentOutput Files"
fName = Dir(fPath & "*.xlsx")

    Do While Len(fName) > 0
        Workbooks.Open fPath & fName
        Set dst = ActiveWorkbook
        fName = Left(fName, InStr(fName, ".") - 1)
              
        Sheets("Managers Summary").Select ' possible error in title format
        Rows("7:11").Select
        ActiveSheet.Copy
        src.Activate
        Rows(RowNum).EntireRow.Select
        ActiveSheet.PasteSpecial xlPasteValues
        Application.CutCopyMode = False
        RowNum = RowNum + 5
        
        dst.Activate
        ActiveWorkbook.Close False
        fName = Dir
        src.Activate
    Loop

Application.DisplayAlerts = True
Application.ScreenUpdating = True


'
End Sub
This is doing a couple wierd things. It opens the first file, selects the rows, then some wierd things happen. First,
Error.png
Attachment 147820

I get this error: Method "PasteSpecial" of object '_Worksheet' failed

Also, it opens up a new workbook with a copy of the sheet "Manager Summary".

Google has failed me, and I have banged on this code for awhile, any help would be greatly appreciated!

Hi
I am trying to consolidate information from 12 different workbooks
each with 12 worksheets (Jan-Dec) into one 'Group' Workbook (also
split by month). At present I am linking the cells as follow:
=[leisureclub1.xls]January!$F$16
=[leisureclub1.xls]January!$F$17
=[leisureclub1.xls]January!$F$18
=[leisureclub2.xls]January!$F$16
=[leisureclub2.xls]January!$F$17
and so on. this is very time consuming. Is there any way i can specify
the worksheet refernce from a cell in my consolidated spreadsheet i.e
=[leisureclub1.xls]"cell A1"!$F$16
=[leisureclub1.xls]"cell A1"!$F$17
This would save me having to link the cells across each of the 12
worksheets.
i hope this makes sense. I think i have confused myself!
Justin


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