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

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Welcome to the Board!

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

HTH,
 

baroha0b

New Member
Joined
Jul 11, 2010
Messages
6
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:

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
What's the purpose of having all the data in one cell? Your current layout is ideal for any analysis you want to do.
 

baroha0b

New Member
Joined
Jul 11, 2010
Messages
6

ADVERTISEMENT

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
 

baroha0b

New Member
Joined
Jul 11, 2010
Messages
6

ADVERTISEMENT

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,
 

gecs

Active Member
Joined
Jan 26, 2009
Messages
320
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:

baroha0b

New Member
Joined
Jul 11, 2010
Messages
6
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
 

gecs

Active Member
Joined
Jan 26, 2009
Messages
320
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:

Watch MrExcel Video

Forum statistics

Threads
1,123,085
Messages
5,599,650
Members
414,325
Latest member
kfg1287

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
Top