Forum Discussion
Align duplicates from two columns to same rows in Excel + additional rows left & right
troyyy Ah, yeah! Forgot about those. Due to the limitations of VLOOKUP and since you don't have access to the newer functions, the easiest would be to place the first keyword column to the left of all others. Done that and added the correct VLOOKUP formulae. See attached file ending with "_rev.xlsx".
If, on the other hand you can not move the columns around, you can make VLOOKUP find a match towards towards the left of the "lookup column", using the technique describe in the attached link.
https://www.myonlinetraininghub.com/excel-vlookup-to-the-left-using-choose
Alternatively, use a combination of INDEX and MATCH. Both methods are demonstrated in the file ending with "......_rev2.xlsx".
troyyy Ah, yeah! Forgot about those. Due to the limitations of VLOOKUP and since you don't have access to the newer functions, the easiest would be to place the first keyword column to the left of all others. Done that and added the correct VLOOKUP formulae. See attached file ending with "_rev.xlsx".
If, on the other hand you can not move the columns around, you can make VLOOKUP find a match towards towards the left of the "lookup column", using the technique describe in the attached link.
https://www.myonlinetraininghub.com/excel-vlookup-to-the-left-using-choose
Alternatively, use a combination of INDEX and MATCH. Both methods are demonstrated in the file ending with "......_rev2.xlsx".
Hello Riny_van_Eekelen,
I'm not sure if I should open a new thread or if it's fine to ask an almost the same question with slightly different situation. A situation which makes the current formula non-usable.
Let me try to explain:
I have Data (a Full Name in Column A) on the left side and Data on the Right side (a Full Name + a lot of columns behind from Column C on.)
Now I want that the Data from all columns on the right side to match with the order of Data on the left side (Column A)
| Column A | Column B | Column C | Column D | Column E | Column F | ||||||||||||||||
| Full Name | Full Name | Verify Status | First Name | Last Name | Full Name | User Social | Job Position | Prospect Location | Add Date | Company Name | Company Url | Company Social | Company Size | Country | Location | State | City | Industry | |||
| Howard Kogan | Howard Kogan | Secret@underscore.vc | not valid | Howard | Kogan | Howard Kogan | https://www.linkedin.com/in/hkogan | Core Member | Boston. Massachusetts. United States | 2021-04-26 13:04:10 | Underscore VC | http://www.underscore.vc | https://www.linkedin.com/company/10791786 | 11-50 | United States | Boston. Massachusetts. United States | MA | Boston | Venture Capital & Private Equity | ||
| Pat Flynn | Todd Gettelfinger | Secret@OrbitMedia.com | unknown | Todd | Gettelfinger | Todd Gettelfinger | https://www.linkedin.com/in/toddgettelfinger | Chief Executive Officer | Chicago. Illinois. United States | 2021-04-26 13:04:10 | Orbit Media Studios. Inc. | https://www.orbitmedia.com | https://www.linkedin.com/company/218994 | 11-50 | United States | Chicago. Illinois. United States | IL | Chicago | Marketing and Advertising | ||
| Todd Gettelfinger | KEVIN INDIG | Secret@kevin-indig.com | not valid | KEVIN | INDIG | KEVIN INDIG | 2021-04-26 13:04:10 | Kevin-indig | http://kevin-indig.com | ||||||||||||
| Matthew Barby | |||||||||||||||||||||
| Mari Smith | Mari Smith | Secret@MariSmith.com | valid | Mari | Smith | Mari Smith | https://www.linkedin.com/in/marismith | Keynote Speaker | San Diego. California. United States | 2021-04-26 13:04:10 | Mari Smith International. Inc. | http://marismith.com | https://www.linkedin.com/company/2140371 | 11-50 | United States | San Diego. California. United States | California | San Diego | Professional Training & Coaching | ||
| Kevin Indig | Pat Flynn | Secret@smartpassiveincome.com | valid | Pat | Flynn | Pat Flynn | 2021-04-26 13:04:10 | Smartpassiveincome | http://smartpassiveincome.com |
How to do it that it looks like this:
| Column A | Column B | Column C | Column D | Column E | Column F | ||||||||||||||||||
| Full Name | Full Name | Verify Status | First Name | Last Name | Full Name | User Social | Job Position | Prospect Location | Add Date | Company Name | Company Url | Company Social | Company Size | Country | Location | State | City | Industry | |||||
Howard Kogan | Howard Kogan | secret@underscore.vc | not valid | Howard | Kogan | Howard Kogan | https://www.linkedin.com/in/hkogan | Core Member | Boston. Massachusetts. United States | 2021-04-26 13:04:10 | Underscore VC | http://www.underscore.vc | https://www.linkedin.com/company/10791786 | 11-50 | United States | Boston. Massachusetts. United States | MA | Boston | Venture Capital & Private Equity | ||||
| Pat Flynn | Pat Flynn | secret@smartpassiveincome.com | valid | Pat | Flynn | Pat Flynn | 2021-04-26 13:04:10 | Smartpassiveincome | http://smartpassiveincome.com | ||||||||||||||
| Todd Gettelfinger | Todd Gettelfinger | secret@OrbitMedia.com | unknown | Todd | Gettelfinger | Todd Gettelfinger | https://www.linkedin.com/in/toddgettelfinger | Chief Executive Officer | Chicago. Illinois. United States | 2021-04-26 13:04:10 | Orbit Media Studios. Inc. | https://www.orbitmedia.com | https://www.linkedin.com/company/218994 | 11-50 | United States | Chicago. Illinois. United States | IL | Chicago | Marketing and Advertising | ||||
| Matthew Barby | |||||||||||||||||||||||
| Mari Smith | Mari Smith | Secret@MariSmith.com | valid | Mari | Smith | Mari Smith | https://www.linkedin.com/in/marismith | Keynote Speaker | San Diego. California. United States | 2021-04-26 13:04:10 | Mari Smith International. Inc. | http://marismith.com | https://www.linkedin.com/company/2140371 | 11-50 | United States | San Diego. California. United States | California | San Diego | Professional Training & Coaching | ||||
| Kevin Indig | KEVIN INDIG | mailto:secret@kevin-indig.com | not valid | KEVIN | INDIG | KEVIN INDIG | 2021-04-26 13:04:10 | Kevin-indig | http://kevin-indig.com |
Attached is a sample excel file.
Cheers
TJ
troyyy Not sure I follow, but if you put a header on the "online Marketer" sheet that matches the headers for similar columns on the "Top 1000....." sheet, you could use the INDEX / MATCH solution provided earlier.
Hey Riny_van_Eekelen
yes, I tried to use the INDEX / MATCH formula you suggested before and tried to adjust it to this situation.
But I simply can't get it working.
The main issue is that yes, I can get Full Names to align with each other but how to also align the other columns after the Full Name?
Basically I'm able to say: Align Full Name "Howard Kogan" from Column A with Full Name "Howard Kogan" from Column C.
But I want to say: Align Full Name "Howard Kogan" from Column A with Full Name "Howard Kogan" from Column C AND the following Data from Column D, E, F and so on.
And this is what I don't understand and where my skill is not good enough to understand what I need to do to also align Data from the other Columns and that they follow Data from Column C basically.
Any thoughts?
troyyy Sorry don't follow. Column A doesn't contain a name.