Apr 13 2021 03:21 AM
Hello,
I would like to know how to align duplicates from two columns to same rows in Excel + additional rows left & right
See Excel File attached or here description of the example:
Data (Keywords) on left site "C" + additional information "A" + "B" aligns with
Data (Keywords) on right site "D" + additional information "E" + "F"
E.g.
Column A | Column B | Column C | |
Row 1
| Search Volume | KW Difficulty Score | Keywords KWTool |
Row 4 | 480 | 71.15 | constant contact alternatives |
aligns with
Column D | Column E | ColumnF | |
Row 1 | Keywords Ahrefs | KW Difficulty Score | Search Volume |
Row 17 | constant contact alternatives | 31 | 250 |
So it looks like this
Search Volume (Average) | KW Difficulty Score | Keywords KWTool | Keywords Ahrefs | KW Difficulty Score | Search Volume |
480 | 71.15 | constant contact alternatives | constant contact alternatives | 31 | 250 |
Any formula that works?
Cheers
TJ
Apr 13 2021 05:13 AM
@troyyy If you are a MS365 subscriber, you could use FILTER as demonstrated towards the far right, in the attached file.
If FILTER is not supported in your Excel version you can revert to VLOOKUP. Come back here if you need more help.
Apr 14 2021 09:47 AM
unfortunately I'm not a MS365 user. I've been looking into VLOOKUP trying to understand it. But I don't understand that formula or at least it doesn't work
I tried to do it - see the excel file.
But for whatever reason it doesn't work.
Any thoughts on that?
Cheers
Torge
Apr 14 2021 09:55 AM
@troyyy Not sure, but have a look at the attached. With VLOOKUP, the lookup value should be in the first column of the lookup range. Done that in column G. The #NA errors indicate that the value in D is not found in C. Does this help?
Apr 14 2021 12:12 PM - edited Apr 14 2021 12:12 PM
Hello @Riny_van_Eekelen,
okay. Now I have the keywords names. But I also want to have the correct "Search Volume" (from Column B) and correct "Keyword Difficulty Score" (from Column A) into Column H & I.
How to do that?
Apr 14 2021 09:12 PM - edited Apr 18 2021 10:53 PM
Solution@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".
Apr 26 2021 03:40 PM
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 | 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
Apr 27 2021 10:49 AM
@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.
Apr 28 2021 08:52 AM
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?
Apr 28 2021 09:11 AM
@troyyy Sorry don't follow. Column A doesn't contain a name.
Apr 14 2021 09:12 PM - edited Apr 18 2021 10:53 PM
Solution@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".