×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Match data from both sheets column and join them to another sheet column

Match data from both sheets column and join them to another sheet column

Match data from both sheets column and join them to another sheet column

(OP)
Hi Everyone,

Will try my best to explain my situation.

Below I have on column A data that repeats itself like AACE001N and so forth.

In the same excel I have another sheet with data on column A which I need to match from the other sheet column A and then copy the data to sheet 1 column B.

For example: everytime on sheet1 I see AACE001N then my column B same row will have data as AACE001N-WA-GH-Closed

I am also attaching the excel data. Thank you for your support!

-

RE: Match data from both sheets column and join them to another sheet column

Wouldn't you just use a simple VLOOKUP in Excel?


---- Andy

There is a great need for a sarcasm font.

RE: Match data from both sheets column and join them to another sheet column

(OP)
Hi Andy,

I am still researching here, below what I got so far but when I git enter nothing happens:

RE: Match data from both sheets column and join them to another sheet column

(OP)
Also I tried using index array but got N/A error, below a screenshot (maybe is because my column B data have no spaces, it has a "-" after each word?):

RE: Match data from both sheets column and join them to another sheet column

I tried to play with your Excel, but I had the same issues.
You have some links in your file, not the values:



That may be part of the problem.
I was trying to do Copy / Paste to find some data in your Excel file by hand, and I could not do it... sad


---- Andy

There is a great need for a sarcasm font.

RE: Match data from both sheets column and join them to another sheet column

You don't need concatenate...

SHEET2!B1: {=INDEX(Sheet1!A:A,MATCH([@AwardNo],LEFT(Sheet1!A:A,LEN([@AwardNo])),0),1)}

...entered as an Array Formula (ctrl + SHIFT + ENTER)

BTW, it takes a while to calculate. Go get yerself a cup of coffee.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Match data from both sheets column and join them to another sheet column

WOW!!!

You're concatenating data from 9 columns for 1243 rows from an external workbook. And then waiting for all the links to calculate. Tic, tic, tic...

Must have taken you quite a while to perform the concatenation and then wait around each time you get new data, not to mention being sure you've accounted for every row. Right?

VERY AKWARD! TIME CONSUMING!

You could accomplish the data acquisition via MS Query, for instance. Set up the query in just a few minutes and refresh might take 5-10 seconds.

Ask me how, if need be.

BTW, I see you have 61 unmatched AwardNo values.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Match data from both sheets column and join them to another sheet column

I'm with Skip, built-in power query allows to complete the task without formulas, much faster. Having both ranges as tables, in single query steps you can (1) duplicate second table's column, (2) split second column with delimiter, extract first part, (3) merge first table with left-outer join, using first table column and second table data in new column, (4) expand merged data, remove unnecessary columns, (5) replace 'null' (if no match) with custom text, (6) output data to workbook.

combo

RE: Match data from both sheets column and join them to another sheet column

(OP)
Hi Everyone,

I was able to get it working, did the following, data I need is on column D:




Thank you all again for your support!

Marc Nascimento

RE: Match data from both sheets column and join them to another sheet column

@ Marclem, it is not satisfying for you to simply state, "I was able to get it working..." after getting various tips leading toward a solution.

Our members want to know exactly what you did to obtain a solution. How did you get the data in column A, while column B appears to be the data from Sheet1?

It seems much more indirect than the solution I posted to be applied to the Sheet1 New column.

Help us out.

Sheet1
Final
AACE001N-WA-GH-Closed
ABBOT001N-LAC-HT-Closed
ABBOT002N-LAC-HT-Closed
ABBOT003N-LAC-HT-Closed
ABBOT004N-LAC-HT-Closed
ABBOT005N-AS-IN-Closed
 

Sheet2
AwardNo   New
AACE001N  =INDEX(Sheet1!A:A,MATCH([@AwardNo],LEFT(Sheet1!A:A,LEN([@AwardNo])),0),1)
AACE001N	
AACE001N	
AACE001N	
AACE001N	
AACE001N	

 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close