Hi Ehab

Unable to download the file.

Can you try attaching the file again?

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 .

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

Sunny

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

Hi Ehab

You are most welcome.

Sunny

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

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.

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

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

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