Vlookup? Pivot Table? How do I match column A and B to contents in column C?

**rushnit** · 08-07-2012, 10:49 AM

Its more complicated than the title leads on...

Column A has the Name of the Company, Column B has the Company's unique record number. These two columns must stay alligned. Because Column C through I, have tasks performed by these companies but only a record number to identify them and they are not in any particular ascending order. Also there are thousands more tasks then there are companies. Some of the tasks are from companies not listed in column A&B and if that is the case I need to get rid of whatever tasks does not match up with columns A&B. Then there will be multiple tasks per company.

example of problem.xlsx

But first, I need to match column A and B to column C without moving C through I or if I do I need to make sure the rows from C through I stay together.

My spreadsheet has lines in the 200k's, its huge. What is the best way to go about finding a solution. If you have one can you please list it! I'm desperate and in a hurry!

Angela B.

ps I've attached a sample

**NBVC** · 08-07-2012, 11:01 AM

In another column, enter formula:

=IFERROR(INDEX(A:A,MATCH(C2,B:B,0)),"")

copied down.

This aligns NAME to ACCOUNTID in column C based on match in column B....

**rushnit** · 08-07-2012, 12:00 PM

it didn't work...it worked in my sample spreadsheet though. what am I doing wrong? Am I pasting the formula wrong. Can I just use my solid cross hold it down and copy all the way down or do I need to copy past the formula differently?

**NBVC** · 08-07-2012, 12:03 PM

Can I just use my solid cross hold it down and copy all the way down

that should work.. it should change the C2 to C3, to C4, etc...

How is it not working? Error? Wrong results? Are you referencing same columns as your sample?

**rushnit** · 08-07-2012, 12:08 PM

Its working!!! You da man!!!
Thank you so much!!!!

---------- Post added at 09:08 AM ---------- Previous post was at 09:04 AM ----------

my spreadsheet is so large it took 5 minutes to show the results! lol

**Cutter** · 08-07-2012, 01:47 PM

@ rushnit

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

**rushnit** · 08-07-2012, 04:09 PM

Okay Cutter I didn't mark it as solved because I ran into an a situation. But I will if we can solve this no problem

NBVC: Out of the 179k lines only 730 returned with data. Does your formula include a solution if I have blanks in column C?

Thanks!

**NBVC** · 08-07-2012, 04:14 PM

Not sure what you mean....

if you are copying formula down, and some cells in column C are blank, then you can use formula:

=IF(C2="","",IFERROR(INDEX(A:A,MATCH(C2,B:B,0)),""))

to get blanks in your C column....

but I am uncertain that this is what you mean?

**rushnit** · 08-07-2012, 04:34 PM

another example.xlsx

some of my tasks don't have ACCOUNTIDS and in column C might be blank, so therefore I won't be able to utilize that data. I'll need to trash it along with any lines that don't match up exactly to what I have in columns A and B.

I've attached another example so you can see more clearly what I'm facing.

**NBVC** · 08-07-2012, 04:39 PM

I am still not following.... what results are expected in the rest of those cells in column J and why?

**rushnit** · 08-07-2012, 04:40 PM

the ultimate goal is to delete colums A and B and replace them with your formula results so that the tasks which only had ACCOUNTIDs to them now have account names. All tasks that don't match up will need to be deleted. I only want tasks that match up to columns A and B and whatever is left over I will delete.

**NBVC** · 08-07-2012, 04:43 PM

So what should go in say J6. Is it a guess?

**rushnit** · 08-07-2012, 04:48 PM

another example.xlsx

Here's what my spreasheet will look like after I clean it up post your formula.

**rushnit** · 08-07-2012, 04:50 PM

J6 could be something I can filter to so it will be easier for me to delete...I hope that made sense lol perhaps just leave it blank or give it a false outcome?

**NBVC** · 08-07-2012, 04:56 PM

The last formula I gave will leave it blank:

=IF(C2="","",IFERROR(INDEX(A:A,MATCH(C2,B:B,0)),""))

or if you want a false outcome:

=IF(C2="","DELETE",IFERROR(INDEX(A:A,MATCH(C2,B:B,0)),""))

then you filter by DELETE in column J.

**rushnit** · 08-07-2012, 05:15 PM

ok, im implementing it now i'll let you know how it turns out... it might take a while because it's so big.

Thank you so much for all your help thus far!

**rushnit** · 08-08-2012, 09:58 AM

It worked and you're wonderful! thank so much this thread is now closed

---------- Post added at 06:58 AM ---------- Previous post was at 06:57 AM ----------

thank you!!!!!!

**NBVC** · 08-08-2012, 10:16 AM

You are welcome. Glad we got resolution

Vlookup? Pivot Table? How do I match column A and B to contents in column C?

LinkBack

Thread Tools

Rate This Thread

Display

Vlookup? Pivot Table? How do I match column A and B to contents in column C?

Re: Vlookup? Pivot Table? How do I match column A and B to contents in column C?

Re: Vlookup? Pivot Table? How do I match column A and B to contents in column C?

Re: Vlookup? Pivot Table? How do I match column A and B to contents in column C?

Re: Vlookup? Pivot Table? How do I match column A and B to contents in column C?

Re: Vlookup? Pivot Table? How do I match column A and B to contents in column C?

Re: Vlookup? Pivot Table? How do I match column A and B to contents in column C?

Re: Vlookup? Pivot Table? How do I match column A and B to contents in column C?

Re: Vlookup? Pivot Table? How do I match column A and B to contents in column C?

Re: Vlookup? Pivot Table? How do I match column A and B to contents in column C?

Re: Vlookup? Pivot Table? How do I match column A and B to contents in column C?

Re: Vlookup? Pivot Table? How do I match column A and B to contents in column C?

Re: Vlookup? Pivot Table? How do I match column A and B to contents in column C?

Re: Vlookup? Pivot Table? How do I match column A and B to contents in column C?

Re: Vlookup? Pivot Table? How do I match column A and B to contents in column C?

Re: Vlookup? Pivot Table? How do I match column A and B to contents in column C?

Re: Vlookup? Pivot Table? How do I match column A and B to contents in column C?

Re: Vlookup? Pivot Table? How do I match column A and B to contents in column C?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions