How to Reformat Data Exported from a Database

[cantab04]

Hi everyone,

I frequently find myself in the position of having data on the same individual appear in many different rows. Each row is unique, however, because it contains different info about this individual. For example, someone might have 3-4 different addresses, and so when I query my database, the export I receive looks like the table below:

I'd like to know if there's a quick way to convert the format above to the one below, which would then allow me to run VLookups on the data.

 

[CA_Punit]

Which version of Excel do you Use

 

[CA_Punit]

Try this

Book1
	A	B	C	D	E	F
2	Jaun	Newyork
3	Jane	Houston
4	Jaun	Chicago
5	Jaun	Philap
6	Jane	Denver
7	Jane	Miami
8
9
10	Jaun		Newyork	Chicago	Philap
11	Jane		Houston	Denver	Miami
12
13
14
Sheet5

Cell Formulas
Range	Formula
C10:H11	C10	=IFERROR(INDEX($B$2:$B$7,AGGREGATE(15,6,IF($A10=$A$2:$A$7,(ROW($A$2:$A$7)-ROW($A$2)+1)),COLUMN($A:$F))),"")
Dynamic array formulas.

 

[Norie]

Where is the data coming from?

 

[cantab04]

Wow, CA_Punit, thanks a lot. It appears this formula does the trick. Really appreciate your help!

 

[CA_Punit]

please update your account detail so that we may know which version of excel you are using

and thanks for feedback

 

[cantab04]

Will do!