Free Microsoft Excel 2013 Quick Reference

Consolidate Text

I don't know if you can even do this.

I have 3 sheets each representing a day. I want the information on each sheet to populate to the Month sheet. I tried to use the consolidate feature but it just told me "No data was consolidated".

I've attached an example. The data on the Month sheet is cut and pasted to show how I would like for it to look.

Post your answer or comment

comments powered by Disqus
Hi all

Wanting to know if you can consolidate text when a given criteria is met.

column A column B

fav Workplace Accommodation
unfav Treatment Operations
fav Monitoring
unfav Water Services
unfav Asset Solutions
fav Desalination Opex Capitalised
fav Asset Management

How do I write a formula that will produce the text

"unfavourable divisions are: Treatment Operations, Water Services and Asset Solutions" and

"favourable divisions are: Workplace Accomodation, Monitoring, Desalination Opex Capitalised and Asset Management".

divisions will change between fav and unfav from month to month.

must be able to do this right??

thanks in advance.

I want to be able to consolidate or append several .txt files into one .txt file (they all have same types of data and layed out the same way). But also have excel list in a worksheet each of the text file names and the number of rows they had. Does anybody have any suggestions? Thanks in advance for your help.

I need to consolidate two different worksheets that contain text. One
worksheet contains the following:

Row A -
Row B - alphanumeric identifier
Row C&D - numbers

The second worksheet contains the following:

Row A - name
Row B - alphanumeric identifier

I need to pull the name from the second worksheet that matches the
alphanumeric identifier in the first worksheet. Any ideas?


Hi I have a zipcode file I am trying to consolidated that has zipcodes and customers. Does anyone have a suggestion on how to consolidate the customers into a single row by zipcode as outlined below

Current Data
Zipcode Customer
00001 Customer 1
00001 Customer 2
00001 Customer 3
00002 Customer 4
00002 Customer 5

Desired output
00001 Customer1, Customer2, Customer3
00002 Customer4, Customer5
etc etc

I truly appreciate any advice on this issue I am struggling with since a pivot table doesnt seem to work on text.


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.


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


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 there,

I need to consolidate 9 worsheets ( with columns A B C rows 1 to 50 ) into one master worksheet.
The data is text only in the three columns.
I have never done macros before and have been searching on google desesperately but cannot make it work!
Could you please kindly help and give me the proper code to make a macro work? I have attached the file.

Many thanks

Problem : How can I consolidate text and numbers from 5 cels in a row to one new cell without using formula. I only wish to place the data all together in one cell F so I have one invoice ID number.

I need to contruct a invoice code for a list of customers which is made up from data in five cells in the same row to the left into one cell to the right.

I think this will require a macro of sorts.



SRNBB MS 524 FB 57


Any help gratefully received.



I am looking for a macro which can ease my work of consolidating responses from different products/teams. I have attached a sample format.

I have to consolidate responses in column D, E and F in Column H. This action needs to be done from row no. 3 to 1000.

Here is the flow chart of the action I want to do with this macro:

For wsSheet1(RowH3:RowH1000)
- Check if cell C3 is having text. If yes, then copy its heading (in Bold with colon) in F3 and then its respective text. In case there is no text ignore and move to next column E.
- Check if column D3 is having text, if yes copy its heading (in Bold with colon) and its respective text in F3 under previous response.
- Perform similar action with E3.

I am not an expert with Macro's so I tried with Record macro. But that doesn't helps because I want to generalize this macro, meaning if I have more than one columns or may be different range of rows, I just change the column and row no. in the macro and use it further.

Please have a look to the attached file. I have tried to work with record macro and results are in column F3. In adjacent column H, I have given the format I desire.

Thanks in advance !!
Your support is appreciated !!



I need to create a spreadsheet that will compile text information from several worksheet into one worksheet. Here's the scenario:

Our sales people fill out projection sheets that include customer name, project name, project amount, potential for close etc. The sales manager would liket too see all of this information on one spreadsheet. How do I take the seven individual worksheets and compile then onto one sheet? Can this be done automatically. i.e. when any salesperson add a prospect it shows up on the summary sheet. The sales folks should only have access to their individual worksheet.

Thanks so much to anyone who can help!!


i have a cloumn with a load of single names in it, is it possible to get this all into one cell (i.e. one line of text), with the ';' character as the seperator?


I am wiriting a VBA macro, to consolidate text into columns. This text is pasted from a pdf file , and is entered into column A, from here I would like the macro to seperate this into columns. The realy problem I have is that i do not know how to tell the macro to seperate text and numbers, also each pdf file has slightly different lenghts of words or numbers

Example below

Any help would be brilliant!

I need to consolidate text data in a pivot table.
I just can not find a way to do this as only numerical functions appears to be possible in pivot.
Year Level textData
2006 High Text_A
2006 High Text_B
2007 Low Text_A

I would like to get the 3 following lines:

A1: B1: 2006 C1: 2007
A2: High B2: Text_Antext_B C2:
A3: Low B3: C3: Text_A

Any help greatly appreciated!

I have an xls file with 8 worksheets of names and other information for students. I'm trying to figure out how to consolidate all these names and other information onto 1 master list that I can view. Is there any way to do this thats built into excel? I've tried using the consolidate function but it seems that is mainly for consolidating numbers and giving you a sum. I'm just looking to basicly copy names and school info from 7 different tabs into 1 tab that can be viewed and printed off.

Thanks in advance.

Hi All,

I have been trying to work out a formula to meet my needs but just cannot seem to find anything that has worked/even remotely came close.

Rundown of my spreadsheet:

- C25:C49 lists cells that contain the following data:

Cell 25 looks like this: =H25&I25&J25&L25&N25&O25&P25&Q25
Cell 26 loosk like ths: =H26&I26&J26&L26&N26&O26&P26&R26
e.t.c e.t.c

effectively, I am compiling many different cells with individual text, sentences to make one long phrase.

This is working fine.

-In Columns D25:D49 (beside consolidated text) I am using the =LEN to calculate the character count of each cell. This is working fine as well

And here is my problem-

I am need of a formula that will randomly pick a cell between C25:C49 that has more than 148 characters and less than 160.

I have searched high and low, googled numerous different searches but still havn't really come up with anything. Decided to go to the gods of Excel!

I would like to thank anybody who is able to help me in advance, it will be much appreciated! Let me know if you require any further info!


I have a large number of seperate files that have much but not all of the
same information in them. Is there any way for me to consolidate them all?
If I can consolidate them is there a way for it to rid the repeats and only
list what isn't repeated? These are files that contains names, contact
numbers, addresses, etc. They are all text without any functions. There are
labels at the top.

In worksheet1 I have
Last First Month
Doe John Sept
smith Jan Oct

In worksheet2 I have
Last First Date Year
Doe John 7 1912
smith Jan 3 1953

I want to consolidate this into one worksheet like this
Last First Month Date Year
Doe John Sept 7 1912
Jones Amy Oct 3 1953

but when I use the consolidate function (sum function) I get this
Last First Month Date Year
7 1912
3 1953

My text fields are empty. What am I missing? Should I be using a different
feature of excel to combine this data?


I have to consolidate about 20 sheets from 20 differents workbooks (always
the first one of a workbook).
The amount of columns are not always the same, so i have used the
"consolidation" option. The problem is that with the consolidation tool the
text does not appear on the consolidate sheet.

what did i do wrong? Or is there an other way to do so?

thanks in advance

Is it possible to consolidate data in multiple workbooks including text columns? Currently the consolidate wizard results in only including numeric data columns. I made sure I used the text to columns process to specifically format the columns as text. Then I tried the min as the function. The consolidation completes but only the numeric columns, and oddly enough the date column, display data.

Side note - I could not figure out how to get the consolidate function to use a combination of columns as the unique key so I created a column specifically as a key column so I could concatenate the product code and store code as a single column of data that could join the two worksbooks - is there an easier way to do this.

workbook 1 has columns key, product code, store code, ship date, order qty
workbook 2 has columns key, product code, store code, ship date, inventory qty, attribute 1, attribute 2

The ship date is specifically a date format. product code, store code, attribute 1, attribute 2 are all text columns.

When I run the consolidate I get all the data from workbook1. I get the key data (which is column 1 and I am guessing it interprets that columns as the row label). I get the date and the inventory qty from workbook 2. The product code and store code display for rows that are in both workbook 1 and workbook2 but are blank for any rows in workbook 2 that are not also in workbook1. Attribute 1 and attribute 2 are blank for all rows.

is there a workaround?


I have to consolidate about 20 sheets from 20 differents workbooks (always
the first one of a workbook).
The amount of columns are not always the same, so i have used the
"consolidation" option. The problem is that with the consolidation tool the
text does not appear on the consolidate sheet.

what did i do wrong? Or is there an other way to do so?
Is there an easy way to write a program that accept to show text as wel,
As the function allready exist?

thanks in advance

The consolidate function works great for combining my worksheets within the spreadsheet, but it does not combine the text data. I do not want to do individual formulas for each cell because this will become a template and that will be too hard to track when 20 people or so are using it. Is there a way to get into the VBA of this function and add additional VBA that would consolidate the text as well? If so, how and what would you do? Any other suggestions? Needs to link to individual spreadsheets so that it is updated automatically as additional info is input. Eventually will add butttons to update data and add some other funcitonality. Do not want to use pivot tables, too cumbersome. any help is appreciated!! (I am not a programmer, so I don't have alot of experience in programming.....Thanks.

I need to consolidate a lot of sheet together but some of the cell are
containing text. I can't find a way to consolidate the sheet in order to see
the text in the consolidate sheet.
Hox do i have do to, if i need the text to appear?

thanks a lot, brgds,

Hey guys, I'm doing a data consolidation of multiple worksheets which contain some stock inventory that we manage. Now I've read a lot about data consolidation online and have done some practice, although from my research I'm finding out that MS Excel does not like to handle text on sum consolidations.

The list which I'm trying to consolidate has the following column fields:
Part number, description, project, quantity

I've made it so each of the worksheets which I'd like to consolidate, have the same name fields and are organized in the name order and manner. I've specified excel to handle the part numbers as "Text", so it would not mistake the part numbers for quantitative values and I've given the cells which I'd like to consolidate a name for each range. Up to this point everything is ok, the problem that I'm having is that:

1. When I try to consolidate the data, excel does not know what to do with the description of each part number, so it adds up the quantity of each part, if multiples are listed, although it leaves a blank for where the description would go.
2. Another problem is that throughout the multiple lists organized, the descriptions do not always match. Some are in English, some in other languages. I'd like excel to accept the first match for the description and merge the quantity in stock based upon the total quantities throughout all consolidated lists.

If anyone can help, I would greatly appreciate it. I've been racking my brain trying to come up with different solutions to handle this. I really like how excel has the "Create links to source data" feature so we can keep track as to who has which inventory.

Thanks once again and I look forward to your responses!

I have got a number of worksheets containing lists of names/ Email addresses
ie. non-data (ie text) values .

I want to consolidate these non-data (ie text) values from cells in
different worksheets - but am failing. I have formatted the separate
worksheets into lists, and then used the 'consolidate' function, but of
course this requires a 'summary function' setting ('SUM', 'COUNT') - I can't
see how to do this when it is a non-numerical value.

Any help?

First of all I'd like to say thanks to everyone who helps out with things like this, I've learned a ton by searching this site and working through my problems with what I find. But now I've got something that seems pretty simple, but I haven't been able to find much to get me started.

I've got a 10 column by 8 row table that contains some duplicate 3-letter text entries, and I'd like to have these entries consolidated down so that each particular 3 character set is shown once in a different column.

It looks something like this-
Col.1 Col.2 Col.3
DDD EEE (blank)
FFF (blank) (blank)

*edit* This didn't come out quite as clear as I'd hoped, if this doesn't make sense let me know and I'll attach an example sheet.

I would like to have it return (In separate cells)-

Also, all 10 columns will not be occupied to begin with. This is for a spreadsheet that tracks yearly data, and I'm leaving extra space for later additions.

Any idea how this could be accomplished, preferably through spreadsheet formulas? Thanks in advance for any input...

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