• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 196
  • 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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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