How to combine several records into one

baroha0b

New Member
Joined
Jul 11, 2010
Messages
6
I would like to combine values from multiple records, from one work sheet, into a single record ; example courses taken:

badeg # name date course-name
1234 xyz 1/01/2009 Math
1234 xyz 6/30/2009 Chem
1234 xyz 1/12/2010 Phys
1234 xyz 6/07/2010 Engl


New sheet should look like:

1234 xyz 1/01/2009 Math 6/30/2009 Chem 1/1/2010 Phys

Appreciate your help

Regards
<!-- / message -->
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Welcome to the Board!

Take a look at Pivot Tables. There's a good example/tutorial in the helpfile.

HTH,
 
Upvote 0
Thanks Smitty, but the Pivot Table,which I tried, will not allow me to have all repeated courses into one cell as shown in my example:

New sheet should look like: ( all comma-delimited list )

1234 xyz 1/01/2009 Math, 6/30/2009 Chem, 1/1/2010 Phys,


in the pivot table , I will get all courses in the column label of the pivot table; i only need to show courses realted for the specific name.
 
Last edited:
Upvote 0
What's the purpose of having all the data in one cell? Your current layout is ideal for any analysis you want to do.
 
Upvote 0
Appreciate your timely response Andrew. The reason to have those multiple records into one cell because I would like to link this file with another and lookup certain information to this master file. As you know if I am to use a v-lookup , I have to have only one occurence of the employee record not many as I show in my example.

I hope this clarified my question

Regards
 
Upvote 0
The Vlookup will be based on the badge # of th emaster sheet ( since I will have only one record instead of several records with same badge#), the rest of the data ,which when combined in one cell, will be part of the this record. So the data will be maintained.

Pls refer to my example which shows how is the current setup & how I would like to have it.

badge # name date course-name
1234 xyz 1/01/2009 Math
1234 xyz 6/30/2009 Chem
1234 xyz 1/12/2010 Phys
1234 xyz 6/07/2010 Engl


New sheet should look like:

1234 xyz 1/01/2009 Math, 6/30/2009 Chem, 1/1/2010 Phys,
 
Upvote 0
Appreciate your timely response Andrew. The reason to have those multiple records into one cell because I would like to link this file with another and lookup certain information to this master file. As you know if I am to use a v-lookup , I have to have only one occurence of the employee record not many as I show in my example.

I hope this clarified my question

Regards

Instead of using the VLOOKUP function, for multi-criteria searches use Alladin's magic LOOKUP formula, something like:
Code:
=LOOKUP(2,1/((name_range="xyz")*(badeg_range=1234)*(course-name_range="Phys")),date_range)
will return the date when "xyz" attended the "Phys" course if it's only one record for all three criteria, or the last date if there are more records for those criteria.

If there are more records for the criteria set you can even create a list with all the records meeting those criteria, by tweaking the formula to make it return the 1st, 2nd, 3rd a.s.o. appearance of those criteria in your table (for more than one criteria only in Excel 2007 or later, because it involves the COUNTIFS function).
 
Last edited:
Upvote 0
Thanks Gecs for your try but the result is not what is required which is very clear in my example: listing all courses taken for a specific employee instead of several rows to appear in several columns or in one cell as comma-delimited . It seems a macro is needed to loop through each employee records and convert the listed courses from rows to columns.

Regards
 
Upvote 0
In the following example I added 3 more records for another badge # to your initial set of data and sorted them by date. In F2 there is a drop-down list created using Data Validation with all the unique badge numbers in your master table (in my example there are only 2). Selecting the badge # from that list in G2 the name is displayed and in I2:N3 up to 6 courses can be displayed using a LOOKUP based formula, as I mentioned before. The number of courses can be extended up to how many you think is necessary only by copying the formulas to the right.

Excel Workbook
ABCDEFGHIJKLMN
1badge #namedatecourse-name*badge #name*123456
21234xyz1/1/2009Math*1234xyzdate1/1/20096/30/20091/12/20106/7/2010**
31235abc3/1/2009Chem***course-nameMathChemPhysEngl**
41235abc6/15/2009Phys**********
51234xyz6/30/2009Chem**********
61235abc1/1/2010Math**********
71234xyz1/12/2010Phys**********
81234xyz6/7/2010Engl**********
Sheet1


Of course, the ranges in the formulas can be replaced by names referring dynamic ranges for the columns involved in the lookups, or you can use only one name for the dynamic range linked to the whole table and use the INDEX function to return only the necessary columns (i.e. if your master table name is "Table1", INDEX(Table1,,1) will return the A2:A8 range used in the formulas, INDEX(Table1,,4) will return the D2:D8 range used in the formulas a.s.o.)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,214,437
Messages
6,119,516
Members
448,903
Latest member
StephMJ

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top