Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

How do I move data from one worksheet to another worksheet using only once column of data to match?

Sheet 1 is small sample of my updated list of cities. I am needing to pull the information that correspodns with each city from Sheet 2 which is a small sample of the information I need to populate my list with. I have used some random names and information for testing purposes.

Sheet 1 I have Albuquerque with blanks for Contact and the various other headings, in Sheet 2 I have Albuquerque with all the information I need in sheet 1.

How can I pull the information over into sheet 1? The Citys which are not represented with data will be populated with another data source file once I am given that information. I need to keep the cell coloring as this will help in the next step of my data sort.

Anyhelp would be great as I have over 27,500 cities.

Thank you



RCSmaster.xlsx
0
manelson05
Asked:
manelson05
  • 5
  • 4
1 Solution
 
krishnakrkcCommented:
Hi,

Define ranges;


City      =Sheet2!$B$2:$B$58
Contact      =Sheet2!$A$2:$A$58
DataRange      =Sheet2!$C$2:$V$58
Headers      =Sheet2!$C$1:$V$1


In C2 on Sheet1 and copied down & across,

=IFERROR(INDEX(DataRange,MATCH(1,IF(Contact=$A2,IF(City=$B2,1)),0),MATCH(C$1,Headers,0)),"")

It's array formula. Confirmed with CTRL + SHIFT + ENTER

Kris
0
 
manelson05Author Commented:
If I do this then all my new data is copied with information  that is not valid. For example City Arizona is not in Sheet 2 it is a new location in Sheet 1. I would like the corresponding city on sheet 1 to pull the data that matches that city name from Sheet 2 and then pull over the contact name, and any of the other categorys that have an X in them.

Is this possible?
0
 
krishnakrkcCommented:
Hi,

It would pull the data from sheet 2 only if a city and contact matches.

Kris
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
manelson05Author Commented:
I understand, but I also need the citys that are not on the source list to stay on Sheet 1, I cant overwrite them.
0
 
krishnakrkcCommented:
Hi,

You are not overwriting anything. Put the formula in C2 and copied down & across

0
 
manelson05Author Commented:
C2 on Sheet 1 or 2?
0
 
krishnakrkcCommented:
Hi,

Sheet1

Kris
0
 
manelson05Author Commented:
Let me try this when I get to work tomorrow.thank you
0
 
manelson05Author Commented:
This worked well after I studied it a bit more.

Thank you!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now