• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Competitors|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Competitors|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Competitors
sp_PrintTopic sp_TopicIcon
Competitors
Avatar
Ehab Ali
Member
Members
Level 0
Forum Posts: 32
Member Since:
September 26, 2018
sp_UserOfflineSmall Offline
1
March 22, 2019 - 5:27 pm
sp_Permalink sp_Print sp_EditHistory

Please can help me to apply below action.

I have one column contain below Companies Names and I want to make each company have other companies as a Competitors.

Avnet, Inc.
B.O.S. Better Online Solutions Ltd.
B/E Aerospace, Inc.

And I want your help to have two columns as below.

Avnet, Inc.                                                  B.O.S. Better Online Solutions Ltd.
Avnet, Inc.                                                  B/E Aerospace, Inc.
B.O.S. Better Online Solutions Ltd.                 Avnet, Inc.
B.O.S. Better Online Solutions Ltd.                 B/E Aerospace, Inc.
B/E Aerospace, Inc.                                      Avnet, Inc.
B/E Aerospace, Inc.                                      B.O.S. Better Online Solutions Ltd.

For More samples, please check attachd file.

sp_AnswersTopicSeeAnswer See Answer
Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
2
March 23, 2019 - 9:18 am
sp_Permalink sp_Print

Hi Ehab

Unable to download the file.

Can you try attaching the file again?

Avatar
Ehab Ali
Member
Members
Level 0
Forum Posts: 32
Member Since:
September 26, 2018
sp_UserOfflineSmall Offline
3
March 23, 2019 - 10:25 pm
sp_Permalink sp_Print

SunnyKow said
Hi Ehab

Unable to download the file.

Can you try attaching the file again?  

Hi SunnyKow,

Many thanks for your reply, I upload the attached file and I hope it will open with you.

Here the steps which I need it, please check.

Input: "A column " contain Company Name "

Steps:

Copy first Company Name which found in "A2 cell" and past in "D2 cell" .

Copy all Companies which exists in all cells under A2, I mean from A3 till the end cell in this A column have data then paste in E2, E3,E4,….

Fill D3, D4, … by company which found in D2 cell.

After that, we do the same think with A3 and so on.

Attached file contain samples .

Avatar
Ehab Ali
Member
Members
Level 0
Forum Posts: 32
Member Since:
September 26, 2018
sp_UserOfflineSmall Offline
4
March 23, 2019 - 11:02 pm
sp_Permalink sp_Print
Please check update attached file.
 
Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
5
March 24, 2019 - 4:36 am
sp_Permalink sp_Print

Hi Ehab

Hope you don't mind a VBA solution.

Sub GetCompetitor()

Dim LastRow As Long
Dim StartRow As Long
Dim i As Long
Dim j As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
StartRow = 2

Range("D:E").ClearContents
Cells(1, 4) = "CompanyName"
Cells(1, 5) = "Competitor"
For i = StartRow To LastRow - 1
For j = i + 1 To LastRow
Cells(StartRow, 4) = Cells(i, 1)
Cells(StartRow, 5) = Cells(j, 1)
StartRow = StartRow + 1
Next j
Next i

End Sub

 

Sunny

sp_AnswersTopicAnswer
Answers Post
Avatar
Ehab Ali
Member
Members
Level 0
Forum Posts: 32
Member Since:
September 26, 2018
sp_UserOfflineSmall Offline
6
March 25, 2019 - 12:27 am
sp_Permalink sp_Print

Sunny

Wow! Thank you so much. This is awesome. This will save me so much time.
Your support is greatly appreciated.

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
7
March 25, 2019 - 9:15 am
sp_Permalink sp_Print

Hi Ehab

You are most welcome.

Sunny

Avatar
Ehab Ali
Member
Members
Level 0
Forum Posts: 32
Member Since:
September 26, 2018
sp_UserOfflineSmall Offline
8
March 27, 2019 - 9:25 am
sp_Permalink sp_Print

Hi Sunny,

Please can you update this macro by below points.

I add new column in the input, the “Product line”, as the target is find the Competitors when the company have the same Product line.

So please I need your help to apply these new points in macro.

  • If the company have the same Product line, we can consider it as Competitor.
  • Determine the Product line causes the Competition.

Example:

Input

Company A                           Product Line

AVX Corporation                Backplane Connectors

AVX Corporation                 Capacitor Kits

AVX Corporation                 Ceramic Capacitors

Kemet Corporation            Aluminum Polymer Capacitors

Kemet Corporation            Capacitor Kits

Kemet Corporation            Ceramic Capacitors

 

The output will be as below

CompanyName                              Competitor                                  Product line_ Competition

AVX Corporation                           Kemet Corporation                Capacitor Kits|Ceramic Capacitors

Note: the Product line column will contain all Product line causes the Competition, separated by |

Please check attached file to get samples.

 

I need it urgently,  please can help me ASAP

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
9
March 27, 2019 - 5:46 pm
sp_Permalink sp_Print

Hi Ehab

What you require is very complicated and difficult to do.

I have to use some helper columns to get the desired result. Columns I and J can be created from the macro I gave to you previously.

Run the macro named COMPETITORS.

I am unable to compare exactly my result against your expected result because of the sequencing but their length are the same.

Good luck.

Avatar
Ehab Ali
Member
Members
Level 0
Forum Posts: 32
Member Since:
September 26, 2018
sp_UserOfflineSmall Offline
10
March 28, 2019 - 1:15 am
sp_Permalink sp_Print sp_EditHistory

Hi Sunny,

You are amazing!

Yes it is working with me, there is one change need please.

I found if the number of Competitor exceed 5, the macro not work correctly. Please run attached file.

So please if you can move below columns to be in second sheet, as by this action there is no limitation in Competitors.

For Matching Purpose  

CompanyName

Competitor

Product Line

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
11
March 28, 2019 - 1:09 pm
sp_Permalink sp_Print sp_EditHistory

Hi Ehab

It is not practical to have unlimited competitors. I have set the output to 50 (even if you have more than 50)

You will need to modify the codes to suit your needs.

Good luck.

Sunny

Avatar
Ehab Ali
Member
Members
Level 0
Forum Posts: 32
Member Since:
September 26, 2018
sp_UserOfflineSmall Offline
12
March 29, 2019 - 7:32 pm
sp_Permalink sp_Print

How kind you are to help me. Thank you very much.
Grateful for your support..

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Valentyn Kristioglo, Tiffany Kang, stuart burge
Guest(s) 11
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 205
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
stuart burge
Bruce Tang Nian
Scot C
Othman AL MUTAIRI
Misael Gutierrez Sr.
Attif Ihsan
Kieran Fee
Murat Hasanoglu
Brett Dryland
Saeed Aldousari
Forum Stats:
Groups: 3
Forums: 24
Topics: 6223
Posts: 27294

 

Member Stats:
Guest Posters: 49
Members: 31919
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.