How to Reformat Data Exported from a Database

cantab04

New Member
Joined
Jan 12, 2018
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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:


1597422764170.png


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.

1597422791990.png
 
Last edited:

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
884
Office Version
  1. 365
Platform
  1. Windows
Which version of Excel do you Use
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
884
Office Version
  1. 365
Platform
  1. Windows
Try this

Book1
ABCDEF
2JaunNewyork
3JaneHouston
4JaunChicago
5JaunPhilap
6JaneDenver
7JaneMiami
8
9
10JaunNewyorkChicagoPhilap
11JaneHoustonDenverMiami
12
13
14
Sheet5
Cell Formulas
RangeFormula
C10:H11C10=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

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
Where is the data coming from?
 

cantab04

New Member
Joined
Jan 12, 2018
Messages
8
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
884
Office Version
  1. 365
Platform
  1. Windows
please update your account detail so that we may know which version of excel you are using

and thanks for feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,122,351
Messages
5,595,646
Members
414,005
Latest member
BradWarr

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