Pull data from imported file.

W

whitaker

New Member
Joined
Mar 7, 2009
Messages
17
I have an excel file which is imported from an outside source and contains all the information pertaining to customers, ie contact person, email address, etc., and changes weekly but the file name will remain the same so I can “replace” the file with the new one when saving to my computer. I have my own excel file which is used as a reporting tool. When I enter a customer name on my spreadsheet, I want a formula that will pull and complete the rest of the information from the import file. So when I list a customer name on my file I want it to find that same customer on the other and fill in the other blank cells. There may be up to 100 customers on the imported file.
Example:
[TABLE="width: 729"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 2"]Imported Spreadsheet
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Customer Name
[/TD]
[TD]New?
[/TD]
[TD]Mailing Address
[/TD]
[TD]City
[/TD]
[TD]State
[/TD]
[TD]Zip
[/TD]
[TD]Annual $
[/TD]
[TD]Contact Name
[/TD]
[TD]email address
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Maxwell Hardware
[/TD]
[TD]No
[/TD]
[TD]2345 Falling Waters Rd.
[/TD]
[TD]Plainville
[/TD]
[TD]AR
[/TD]
[TD]98524
[/TD]
[TD]230,000
[/TD]
[TD]James Lee
[/TD]
[TD]lee.j@gmail.com
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Tom's Feed & Seed
[/TD]
[TD]Yes
[/TD]
[TD]4682 Main Street
[/TD]
[TD]Jonesburg
[/TD]
[TD]AR
[/TD]
[TD]98539
[/TD]
[TD]165,000
[/TD]
[TD]Tom Leavens
[/TD]
[TD]tlea@gmail.com
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 551"]
<tbody>[TR]
[TD][/TD]
[TD]My Spreadsheet
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD][/TD]
[TD]F
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Customer Name
[/TD]
[TD]Annual $
[/TD]
[TD]City
[/TD]
[TD="colspan: 2"]Contact Name
[/TD]
[TD="colspan: 2"]email address
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Tom's Feed & Seed
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="colspan: 2"]
[/TD]
[TD="colspan: 2"]
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
maybe like....


Excel 2010
ABCDEF
1Customer NameAnnual $CityContact Nameemail address
2Tom's Feed & Seed165000JonesburgTom Leavenstlea@gmail.com
Sheet2
Cell Formulas
RangeFormula
B2=INDEX(Sheet1!$A$2:$I$3,MATCH(Sheet2!$A2,Sheet1!$A$2:$A$3,0),MATCH(Sheet2!B$1,Sheet1!$A$1:$I$1,0))
C2=INDEX(Sheet1!$A$2:$I$3,MATCH(Sheet2!$A2,Sheet1!$A$2:$A$3,0),MATCH(Sheet2!C$1,Sheet1!$A$1:$I$1,0))
D2=INDEX(Sheet1!$A$2:$I$3,MATCH(Sheet2!$A2,Sheet1!$A$2:$A$3,0),MATCH(Sheet2!D$1,Sheet1!$A$1:$I$1,0))
E2=INDEX(Sheet1!$A$2:$I$3,MATCH(Sheet2!$A2,Sheet1!$A$2:$A$3,0),MATCH(Sheet2!E$1,Sheet1!$A$1:$I$1,0))
 
Upvote 0
Thank You, it works perfectly except a couple of the Labels used in the Header Row are different. The imported file (Sheet 1) may say "Gross Annual Income" and my file (Sheet 2) is Labeled "Annual $".
 
Upvote 0
As I noted in above reply, I ran into problems with header labels and I'm not allowed to change my report labels to match the imported file labels. So here’s an example with variations of labels for same info. I am not familiar with “INDEX” and “MATCH” usage to alter the formula you gave. Thanks so much for the help.
[TABLE="width: 868"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 2"]File 1 Name: 2013 PCB EMT
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]There's only 1 sheet in this file.
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]info not needed in other columns
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]K
[/TD]
[TD]P
[/TD]
[TD]Q
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Name
[/TD]
[TD]New
[/TD]
[TD]Address
[/TD]
[TD]City
[/TD]
[TD]ST
[/TD]
[TD]Zip
[/TD]
[TD]G.A.I.
[/TD]
[TD]Contact Name
[/TD]
[TD]Contact Email
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Tom's Feed & Seed
[/TD]
[TD]Y
[/TD]
[TD]4682 Main Street
[/TD]
[TD]Jonesburg
[/TD]
[TD]AR
[/TD]
[TD]98539
[/TD]
[TD]165,000
[/TD]
[TD]Tom Leavens
[/TD]
[TD]tlea@gmail.com
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Maxwell Hardware
[/TD]
[TD]N
[/TD]
[TD]2345 Waters Rd.
[/TD]
[TD]Plainville
[/TD]
[TD]AR
[/TD]
[TD]98524
[/TD]
[TD]230,000
[/TD]
[TD]James Lee
[/TD]
[TD]lee.j@gmail.com
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Gloria's Fashions
[/TD]
[TD]Y
[/TD]
[TD]1461 1st Street
[/TD]
[TD]Stillwell
[/TD]
[TD]VA
[/TD]
[TD]43592
[/TD]
[TD]92,000
[/TD]
[TD]Gloria Webb
[/TD]
[TD]gfashions@yahoo.com
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Deel Deal Makers
[/TD]
[TD]Y
[/TD]
[TD]2374 Main Street
[/TD]
[TD]Jonesburg
[/TD]
[TD]AR
[/TD]
[TD]98539
[/TD]
[TD]50,000
[/TD]
[TD]Mike Deel
[/TD]
[TD]mikedeel@gmail.com
[/TD]
[/TR]
[TR]
[TD]99
[/TD]
[TD]Circle W Farms
[/TD]
[TD]N
[/TD]
[TD]RR 2, Box 432
[/TD]
[TD]Glenston
[/TD]
[TD]NC
[/TD]
[TD]38952
[/TD]
[TD]225,000
[/TD]
[TD]Glen Bailes
[/TD]
[TD]cwfarms@hotmail.com
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]"N" means they're "Returning", "Y" means they're "New"
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 799"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 2"]File 2 Name: 2013 PCB TD
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]There's only 1 sheet in this file.
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]G
[/TD]
[TD="colspan: 2"]H
[/TD]
[TD]I
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Business Name
[/TD]
[TD]New/ Returning
[/TD]
[TD]Physical Address
[/TD]
[TD]City
[/TD]
[TD]Potential $
[/TD]
[TD="colspan: 2"]Contact Name
[/TD]
[TD]Email Address
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Maxwell Hardware
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Deel Deal Makers
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Tom's Feed & Seed
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25
[/TD]
[TD]may have less than 25 accounts
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]List your Business Names alphabetically
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
OK, here is how you do it!!


Excel 2010
ABCDEFG
1Business NameNew/ ReturningPhysical AddressCityPotential $Contact NameEmail Address
2Maxwell HardwareN2345 Waters Rd.Plainville230,000James Leelee.j@gmail.com
3Deel Deal MakersY2374 Main StreetJonesburg50,000Mike Deelmikedeel@gmail.com
4Tom's Feed & SeedY4682 Main StreetJonesburg165,000Tom Leavenstlea@gmail.com
Sheet2
Cell Formulas
RangeFormula
B2=INDEX(Sheet1!$B$2:$I$6,MATCH(Sheet2!$A2,Sheet1!$A$2:$A$6,0),MATCH("New",Sheet1!$B$1:$I$1,0))
C2=INDEX(Sheet1!$B$2:$I$6,MATCH(Sheet2!$A2,Sheet1!$A$2:$A$6,0),MATCH("Address",Sheet1!$B$1:$I$1,0))
D2=INDEX(Sheet1!$B$2:$I$6,MATCH(Sheet2!$A2,Sheet1!$A$2:$A$6,0),MATCH("City",Sheet1!$B$1:$I$1,0))
E2=INDEX(Sheet1!$B$2:$I$6,MATCH(Sheet2!$A2,Sheet1!$A$2:$A$6,0),MATCH("G.A.I.",Sheet1!$B$1:$I$1,0))
F2=INDEX(Sheet1!$B$2:$I$6,MATCH(Sheet2!$A2,Sheet1!$A$2:$A$6,0),MATCH("Contact Name",Sheet1!$B$1:$I$1,0))
G2=INDEX(Sheet1!$B$2:$I$6,MATCH(Sheet2!$A2,Sheet1!$A$2:$A$6,0),MATCH("Contact Email",Sheet1!$B$1:$I$1,0))
 
Upvote 0
You must log in or register to reply here.

Similar threads

Imposter
  • Question Question
VBA to import XML files from multiple subfolders of a folder and save imported data to XLSM files by folder
2
Replies
15
Views
6K
Imposter
Imposter
S
  • Question Question
Merging contact info from different exports (sticking point: phone/address/email type column + 1 data column vs. multiple data columns)
Replies
1
Views
767
sebby_joe
S
D
  • Question Question
Transposing non-uniform set of data from one column into rows
Replies
2
Views
975
drspanklebum
D
S
  • Question Question
Extract data from separate worksheet
Replies
1
Views
884
searnold56
S
A
  • Question Question
Macro to extract data from report
2 3
Replies
20
Views
3K
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

Share this page

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