Free Microsoft Excel 2013 Quick Reference

Match and concatenate Results

I have a worksheet with two columns, Grps and Mems. Mems column
contain list of names, each name is a member of multiple grps.
see Figure A
*****************Figure C ********
********Figure B******
***Figure A***
Grps: Mems: Mems1: Grps1:
RN-Ex Alba Alba RN-Ex
RN-Us pual Pual RN-Us
RN-Us Alex Alex RN-us
RN-BO Amy Amy RN-BO, RN-Ex
RN-Ex Amy John RN-Br, RN-us
RN-Br John Sara RN-BN, RN-us
RN-Us John Justin RN-us
RN-BN Sara Anselme RN-BO, RN-ex,RN-us
RN-Us Sara
RN-Us Justin
RN-BO Anselme
RN-Ex Anselme
RN-Us Anselme

Figure B, I added Mems1 column using Advance filter, copy to another
location and unique unique records only switches.

Figure C is where I would like to end up with, but don't know how to
properly nest/combine If, lookup, match and concatenate formulas.

Any idea?

Thanks -Mike

I’ve searched the forum but can’t find the answer to this question… I’m sure its easy given some of the amazing stuff I’ve seen you lot do.

I have two sets of two columns which contain numbers as text with trailing spaces and I want to concatenate column A and B and concatenate column F and G, and then compare them, and sumif value in column I if they are the same - giving the answer in the same row as the matching data in column A and B.. but in column J.

I hope i've explained myself..

I’ve seen sum product and haven’t been able to get it to work at all. I have been adding the trim() function in around the column references to see if that would work but am failing…

E.g.

A = 2000 text
B = 1234 text

F = 2000 trailing spaces, text
G = 1234 trailing spaces, text

I = 10,000

Answer in column J

I want 20001234 = 20001234 = 10,000 for the whole column.

I don't want to change the original data as its externally sourced and used for other links etc.. This would save a few hours a week if i ( more correctly, you ) could get this to work.

Many thanks. chuf

Hello all,

Using Excel 2003 SP2.

I have 2 seperate tables. Want to lookup Sheet 1 A2 series then lookup Sheet 1 B2 series and then match up to Sheet 2 A2 and Sheet 2 B2 series and return the data from Sheet 2 3rd column. Can someone show me an example on how to use vlookup and concatenate together?

If it can be done for 2 columns...can it be done in 3?

Thanks,
Eric in PA


My data looks as so:

Data ColA ColB ColC ColD
A 123 Zebra Monkey
B 456 Dog Cat
C 123 Mouse Rabbit
A 789 Horse Cow
C 789 Cat Dog

I'm trying to put an array formula into ColB in the table below that will
look in
A1 (in the table below) and find a match in ColB of the data above, AND if
ColA of the data above is equal to"A", concatenate the values in ColC and
ColD - as shown below.

Table
123 ZebraMonkey
456
789 HorseCow

So the formula needs to find a match to cell A1 in the table in ColB of the
data, and then check if the adjacent value in ColA of the data is equal to
"A", concatenate the adjacent values in ColC and ColD of the data.

Is it possible ? Thank you in advance.


My data looks as so:

Data ColA ColB ColC ColD
A 123 Zebra Monkey
B 456 Dog Cat
C 123 Mouse Rabbit
A 789 Horse Cow
C 789 Cat Dog

I'm trying to put an array formula into ColB in the table below that will
look in
A1 (in the table below) and find a match in ColB of the data above, AND if
ColA of the data above is equal to"A", concatenate the values in ColC and
ColD - as shown below.

Table
123 ZebraMonkey
456
789 HorseCow

So the formula needs to find a match to cell A1 in the table in ColB of the
data, and then check if the adjacent value in ColA of the data is equal to
"A", concatenate the adjacent values in ColC and ColD of the data.

Is it possible ? Thank you in advance.

I have a spreadsheet in which I concatenate the values in Columns D,E&F into column M. In column L I the look to see if there is any duplicates. I need this to be an exact duplicate match and can't be close matches, however I've tried 3 different formulas (see cells L1, L3 and L5 on attached), but each of these still gives close matches.

I've tried deconstructing each of these formulas, but still always end up with close matches. Can any help me with what I'm doing wrong?

Hi All,

New to forum, looking for some help with a macro that would look up duplicate cells in column A, merge the rows based on that cell and concatenate any data in the cells that did not match.

Kinda difficult to explain, need it for a mail merge to stop sending multiple letters to the same client. For example, the grid looks a bit like this:

0001| Mr Joe Blogs | Henry | Dog|
0002| Mr David Cameron| Cleg| Dog|
0003| Mr John Stewart| Charlie| Dog|
0001| Mr Joe Blogs| Alice| Horse|
0002| Mr David Cameron| Gove| Dog|
0003| Mrs Allison Stewart| Ella| Cat|
0004| Mr Paul Stewart| Pancho| Dog|

With a macro producing:

0001| Mr Joe Blogs| Henry, Alice| Dog, Horse|
0002| Mr David Cameron| Cleg, Gove| Dog|
0003| Mr John Stewart, Mrs Allison Stewart| Charlie, Ella| Dog, Cat|
0004| Mr Paul Stewart| Pancho| Dog|

The spread sheet will have many columns and 1000s of rows.

Many Thanks,
John

Hi,

As per title I'm struggling to make this lookup. In fact, I'm not even sure of the best approach (array formula? UDF?).

I need to basically Vlookup all matching entries in a closed workbook and concatenate them with commas between. Eg:

1          Bob
2          Jane
3          Fred
3          Sarah
3          Robert
4          Rachel
4          Lilly
5          Yyvonne
6          Ted
Function(3) returns Fred, Sarah, Robert
Function(4) returns Rachel, Lilly
Function(5) returns Yyvonne

Any help would be gratefully received.

Thanks,

Chinchin

Hi Excel forummers, would like to seek your advice on how to return the top 5 results in a set of data.

The file can be found here: https://docs.google.com/open?id=0BzQ...1JKSEFGdHJlX00

Extra info are in cells A1, AN1, AN9, BC9

In a nutshell, I have data that needs to be sorted but some intermediate to advanced (to me) excel formula needs to be used to sort the ranking properly. I've already used index, match and large but it does not suffice.

I think rank, concatenate and possibly some other formulas might be required. If you have any idea, please suggest and I will be grateful. I am also open to VBA formulas

Jason

Added
Posted on http://www.mrexcel.com/forum/showthr...=1#post3209858,
http://www.excelforum.com/excel-gene...-formulas.html and
http://chandoo.org/forums/topic/use-...x-match-or-vba

I have a single worksheet that I would like to compare and concatenate the results from two columns. When there is a match I want to place the unique values in a third column. I would like a macro to perform this function. I have seen one that does the concatenation but leaves the original records. Please note that there is data in the other columns but M, N and O is where I want to work from. Any help would be appreciated.

Example:

Currently I have (8 Records):
Columns
M N O
Apple1 Apple1 1200
Apple1 Apple 1 2400
Apple1 Apple 1 5500
Orange2 Orange2 3650
Orange2 Orange2 3700
Orange2 Orange2 3800
Cherry3 Cherry3 2500
Corn1 Corn1 8500

What I would like to see (4 records) :

Columns
M N O
Apple1 Apple1 1200, 2400, 5500
Orange2 Orange2 3650, 3700, 3800
Cherry3 Cherry3 2500
Corn1 Corn1 8500

I am trying to find the closest match of two sets of data.

In each file, there are two two columns (first column contains name which must be matched exactly and second column contains date which must be matched to the closest one). I tried using concatenate to combine the two columns and find the closest match using character matches (VBA program posted previously) but it does not work well with my data.

Attached is a sample of the data I am working on.

It has been giving a lot of headache working with that. Any help would be greatly appreciated.

Thank you in advance.

James

Hi

This is a bit tricky, so I've attached a sample workbook to explain what I mean.

I've got 5 cells in column B on Sheet1 with the following values: test1, test2, test3, test4, test5. I would like to concatenate these values and the word "Expenses", and then check these new values against all the sheets in the workbook to see if each of these values match each sheet name. For example, does the value "test1 Expenses" match a sheet name in the workbook?

If it doesn't, then check the next sheet. If a match is found, then copy the value in cell A1 on the found sheet back to Sheet1. To determine where on Sheet1 to place this value, match the found sheet name to the values in column B on Sheet1 + "Expenses", and paste the value 2 columns over and 2 rows down.

I know this sounds terribly complicated, so let me give an example of the process, given the attached workbook. The code should do the following:
(1) Concatenate the values in column B on Sheet 1 with the word "Expenses".
(2) Look through each sheet in the workbook and find a sheet matching each concatenated value.
(3) When a matching sheet name is found, copy the value in A1 on that sheet.
(4) Go back to Sheet1 and match the found sheet name to the concatenated values in column B; i.e., match the sheet name "test1 Expenses" to the concatenation of "test1" in column B and "Expenses".
(5) When this match has been found, paste the copied value 2 columns over and 2 rows down from the matching cell.

Hope this somehow makes sense! I know I should really be doing this project in Access, but I haven't learned it well enough yet and need to see this one through in Excel, so any help would be greatly appreciated. I would have used formulas instead of code, but the problem is that the referenced sheets may not always exist.

Better stop there.

Many thanks
Pete

Hi,

I have different named ranges for various categories (Products, Authors, Vehicles, Projects, and many more) on a sheet. Each range contains 2 columns; item (falling under the relevant category) and it's unit price. On the same sheet I have defined a 'table' containing all the category ranges that I will be using and on the very next column I have defined the actual named range that I have defined for the category. If you read on till the problem section, you'll hopefully understand why I have taken this approach (besides, I am finding the OFFSET, MATCH, and other functions a bit confusing and thus this approach)

On another, sheet (which I am using for data entry), I have columns:

Category, Item, Qty., Total Price

...the user selects an category, then an item falling under the selected category, and then enters the required quantity. For Category and Item, I am using Data Validation and the neat INDIRECT tip and till here all is working fine.

The problem starts here (in the Total Price column):
Suppose the user has selected Vehicle as the Category (in cell A2) and then Benz Model XYZ (in cell B2) as the item.

I was trying to use the '&' operator to concatenate the selected category e.g 'Vehicle' and concatenate it with the word list i.e. My named range for the category range is called Categorylist. Similarly my named range for the Projects range is called Projectlist and so on...

=VLOOKUP(B2,A2&list,2,FALSE)

It's the bold portion in the formula above which is causing all the problem
FYI, the 2nd column of each range contains the price of the item.

I'll appreciate your help and suggestions. Also, I will begratefull if you can refere me to some post/URL from where I can learn [in simple english ;-)] about the MATCH, OFFSET, and INDEX functions

Hi all,

I have an excel spreadsheet that should have one record for each artifact in a museum collection. The problem is that the museum has consolidated this information from several different sources into one spreadsheet and now there are many duplicate records. They want all the duplicate records removed so that there is just one record for each artifact, BUT there may be different pieces of information in each of the duplicate records. So I want to do the following:

- sort records based on Accession Number (column A)
- find duplicate Accession Number records
- determine which fields (columns) within a duplicate record are unique and concatenate those entries into one master record for each Accession Number
- delete the duplicate Accession Number records

In the attached sample sheet, for Accession Number 66-1-100, we have 6 duplicate records. In the columns, we have information which in some of the records is duplicated, in some it is unique and in some it is missing completely. The museum wants just one master record for each Accession Number and they want all the data from the duplicate records concatenated into one and all the duplicates and blanks discarded.

What I've done so far:
- used the Subtotal feature in Excel to insert a line below each set of Accession Numbers and Count the number of records with matching Accession Numbers.
- from another source, I found the following bit of VBA code which removes duplicate entries from each column within the duplicate records. For the range, I use the OFFSET function to select the cells within the group of duplicate records. In this case B47 is where the Count results of the Subtotal feature go and C47 is my current location.

	VB:
	
 DelDups() 
    Dim rngSrc As Range 
    Dim NumRows As Integer 
    Dim ThisRow As Integer 
    Dim ThatRow As Integer 
    Dim ThisCol As Integer 
    Dim J As Integer, K As Integer 
     
    Application.ScreenUpdating = False 
     
    Set rngSrc = ActiveSheet.Range("OFFSET(C47,-$B47,0,1,1):OFFSET(C47,-1,0,1,1)") 
     
     
    NumRows = rngSrc.Rows.Count 
    ThisRow = rngSrc.Row 
    ThatRow = ThisRow + NumRows - 1 
    ThisCol = rngSrc.Column 
     
     'Start wiping out duplicates
    For J = ThisRow To (ThatRow - 1) 
        If Cells(J, ThisCol) > "" Then 
            For K = (J + 1) To ThatRow 
                If Cells(J, ThisCol) = Cells(K, ThisCol) Then 
                    Cells(K, ThisCol) = "" 
                End If 
            Next K 
        End If 
    Next J 
     
     
    Application.ScreenUpdating = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
- and also from another source, I have a user-defined function that concatenates & adds a delimiter between each data element in each column of the duplicate records.

	VB:
	
) 
    Dim r As Range 
     
    Application.Volatile 
    For Each r In myRange 
        If Len(r.Text) > 0 Then 
            Concat = Concat & r & myDelimiter 
        End If 
    Next r 
    If Len(myDelimiter) > 0 Then 
        Concat = Left(Concat, Len(Concat) - Len(myDelimiter)) 
    End If 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
So here are my questions... I'd really like to have this set up so that it runs automatically through each column for each set of duplicate records, but to do that I think I need to:
- tell my DelDups macro code to loop through the column range for each record
- include my CONCAT formula in the VBA macro
- and once all is done, I want the duplicate records to be deleted, leaving only one record for each Accession Number that includes all the data that was in each of the duplicate records.

I don't really know how to do any of the three things above and I have the feeling there's a much easier way to do all this! However, I am a total VBA nocie and I'm a bit stumped! Thanks in advance for any help.

The sample I am attaching has the first 50 or so records. The complete excel file has about 27,000 rows and 69 columns. The museum believes there should be around 4,000 records once all the duplicates are removed.

Hi all

I'm trying to figure out how I can return an index to an array of values
using "MATCH".

It should be a simple MATCH function but after digging through the questions
& answers on "match" and trying using help for a couple of hours I gave up.

I have an array, {"NONE","NEGLIGIBLE","MILD","MODERATE","HIGH","SEV ERE"} and
want to match this array against the value out of a cell.

Problem is the double-quotes, as far as I can tell.

This function below works,
=MATCH("HIGH",{"NONE","NEGLIGIBLE","MILD","MODERAT E","HIGH","SEVERE"},1)

But these functions below always fail, the first is an attempt to use a
literal value, the second is an attempt to use a value out of a cell

=MATCH(CONCATENATE("""",HIGH,""""),{"NONE","NEGLIG IBLE","MILD","MODERATE","HIGH","SEVERE"},)

MATCH(CONCATENATE("""",N13,""""),{"NONE","NEGLIGIB LE","MILD","MODERATE","HIGH","SEVERE"},)

What am I doing wrong ?

I have been doing this manually but I'm sure there must be a way of doing it
automatically:
on a sheet with cells containing a school form reference (eg '7A') with an
abbreviation for 1) subject 2) teacher and 3) room (all three in separate
columns!)

I would like to be able to (automatically!) look up and read the cells that
contain say '7A' and concatenate the three strings into one cell.

Later I can remove the extra occurrences of the form ID (eg '7A')

I hope this is clear enough
Thank you

I have two workbooks, workbook one contains a list of employee numbers,
firstname, lastname. Workbook two contains the employee number. I would like
to index workbook one, matching on the emp number and concatenate firstname
lastname into a single cell. Is that possible with a formula or do I need a
macro?

Thanks in advance!

Hi everyone, thanks in advance for the help. I'm managing multiple calendars in excel that I have linked to a sharepoint website through a list. The different calendars share a lot of the information, so a lot of the events on the calendar overlap. I have a list in an excel sheet that is linked to sharepoint and I create calendars on sharepoint with that. The list has a column for each of the calendars and it reads yes or no depending on if that event applies to that calendar. As of now I'm using a concatatenate if function that I found online to cocnatenate all the events into one cell on another sheet if the date matches and there is a "yes" in the column for that particular calendar. Now, I need each of the events to be color coded and I need a way to split the concatenated text into multiple cells or create another way of achieving my end result.

Any help would be greatly apreciated. Thanks everyone. Here is the calendar that I need to pull the events into from the list. Quarterly Calendar_Master.xlsx

I have an index and match formula:

=INDEX('[Live Competitor Database.xlsx]Competitors'!$AQ:$AQ,MATCH((LEFT($G5,LEN($G5)-2)&J$1),'[Live Competitor Database.xlsx]Competitors'!$C:$C,1),1)

Column AQ in the Live Competitor Database is a list of opening times, the match value (after chopping it down and concatenating it with another) is bascially a section of postcode (column G) and brand (column J), the forumla should match the concatenated value to the same in column C and return the opening time in column AQ.

Now there are 2 things to note:

1. When there is an exact match to the concatenated value it does not return the same but something very similar (e.g. instead of returning 'WS1 1Natwest', it returns 'WS1 1Barclays') - I can not work out why it does this?

2. There will not always be an exact match, but a value very close is still returned? (e.g. 'DL1 1Natwest' does not exist so it returns 'DL1 1Darlington') - I would prefer a non-return if there is no match?

Hello -

I'm hoping someone can help me come up with a solution to a situation I run into often.

Lets say I have a list of employees in one worksheet and, in a second worksheet, a list of their degrees. I want to do a vlookup to bring over the degree information for each employee, keying off of their employee ID. Would be simple enough, except that an employee may have multiple degree entries or rows, so when I do the vlookup it will only bring over the first match it comes to. The only thing I can think of to try would be to try and merge the multiple rows of degree information into one, perhaps using a if/then and concatenate combination. If anyone has any ideas on how to handle this, I'd LOVE to hear them and be very grateful!

I've included below a sample of what the degree page looks like.

Thanks!

ID Degree Issue Date Major
1 College Credits 1999-08-23 Mechanical Eng
1 Bachelor of Science 2001-12-01 Mechanical Eng
2 Bachelor of Engineering 2001-12-20
2 Master of Engineering 2003-08-08 Mechanical Eng
3 Bachelor of Science 2001-05-31 Mechanical Eng
3 Bachelor of Science 2001-05-31 Physics