SOLVED

Align duplicates from two columns to same rows in Excel + additional rows left & right

Copper Contributor

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 AColumn BColumn C

Row 1

 

Search VolumeKW Difficulty ScoreKeywords KWTool
Row 448071.15constant contact alternatives

 

aligns with

 

 Column DColumn EColumnF
Row 1Keywords AhrefsKW Difficulty ScoreSearch Volume

Row 17

constant contact alternatives31250

 

 

So it looks like this

 

Search Volume (Average)KW Difficulty ScoreKeywords KWToolKeywords AhrefsKW Difficulty ScoreSearch Volume
48071.15constant contact alternativesconstant contact alternatives31250

 

Any formula that works?

 

Cheers

TJ

 

9 Replies

@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.

Hey @Riny_van_Eekelen 

 

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

@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?

 

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? 

best response confirmed by troyyy (Copper Contributor)
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". 

 

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 AColumn BColumn CColumn DColumn EColumn F                
Full Name Full NameEmailVerify StatusFirst NameLast NameFull NameUser SocialJob PositionProspect LocationAdd DateCompany NameCompany UrlCompany SocialCompany SizeCountryLocationStateCityIndustry 
Howard Kogan Howard KoganSecret@underscore.vcnot validHowardKoganHoward Koganhttps://www.linkedin.com/in/hkoganCore MemberBoston. Massachusetts. United States2021-04-26 13:04:10Underscore VChttp://www.underscore.vchttps://www.linkedin.com/company/1079178611-50United StatesBoston. Massachusetts. United StatesMABostonVenture Capital & Private Equity
Pat Flynn Todd GettelfingerSecret@OrbitMedia.comunknownToddGettelfingerTodd Gettelfingerhttps://www.linkedin.com/in/toddgettelfingerChief Executive OfficerChicago. Illinois. United States2021-04-26 13:04:10Orbit Media Studios. Inc.https://www.orbitmedia.comhttps://www.linkedin.com/company/21899411-50United StatesChicago. Illinois. United StatesILChicagoMarketing and Advertising
Todd Gettelfinger KEVIN INDIGSecret@kevin-indig.comnot validKEVININDIGKEVIN INDIG   2021-04-26 13:04:10Kevin-indighttp://kevin-indig.com        
Matthew Barby                     
Mari Smith Mari SmithSecret@MariSmith.comvalidMariSmithMari Smithhttps://www.linkedin.com/in/marismithKeynote Speaker San Diego. California. United States2021-04-26 13:04:10Mari Smith International. Inc.http://marismith.comhttps://www.linkedin.com/company/214037111-50United StatesSan Diego. California. United StatesCaliforniaSan DiegoProfessional Training & Coaching
Kevin Indig Pat FlynnSecret@smartpassiveincome.comvalidPatFlynnPat Flynn   2021-04-26 13:04:10Smartpassiveincomehttp://smartpassiveincome.com        

 

How to do it that it looks like this:

 

Column AColumn BColumn CColumn DColumn EColumn F                  
Full Name Full NameEmailVerify StatusFirst NameLast NameFull NameUser SocialJob PositionProspect LocationAdd DateCompany NameCompany UrlCompany SocialCompany SizeCountryLocationStateCityIndustry   

Howard Kogan

 Howard Kogansecret@underscore.vcnot validHowardKoganHoward Koganhttps://www.linkedin.com/in/hkoganCore MemberBoston. Massachusetts. United States2021-04-26 13:04:10Underscore VChttp://www.underscore.vchttps://www.linkedin.com/company/1079178611-50United StatesBoston. Massachusetts. United StatesMABostonVenture Capital & Private Equity  
Pat Flynn Pat Flynnsecret@smartpassiveincome.comvalidPatFlynnPat Flynn   2021-04-26 13:04:10Smartpassiveincomehttp://smartpassiveincome.com          
Todd Gettelfinger Todd Gettelfingersecret@OrbitMedia.comunknownToddGettelfingerTodd Gettelfingerhttps://www.linkedin.com/in/toddgettelfingerChief Executive OfficerChicago. Illinois. United States2021-04-26 13:04:10Orbit Media Studios. Inc.https://www.orbitmedia.comhttps://www.linkedin.com/company/21899411-50United StatesChicago. Illinois. United StatesILChicagoMarketing and Advertising  
Matthew Barby                       
Mari Smith Mari SmithSecret@MariSmith.comvalidMariSmithMari Smithhttps://www.linkedin.com/in/marismithKeynote Speaker San Diego. California. United States2021-04-26 13:04:10Mari Smith International. Inc.http://marismith.comhttps://www.linkedin.com/company/214037111-50United StatesSan Diego. California. United StatesCaliforniaSan DiegoProfessional Training & Coaching   
Kevin Indig KEVIN INDIGsecret@kevin-indig.comnot validKEVININDIGKEVIN INDIG   2021-04-26 13:04:10Kevin-indighttp://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.

1 best response

Accepted Solutions
best response confirmed by troyyy (Copper Contributor)
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". 

 

View solution in original post