Log In
Register
Home
September 26, 2018
Offline
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.
See Answer
VIP
Trusted Members
June 25, 2016
Offline
Hi Ehab
Unable to download the file.
Can you try attaching the file again?
September 26, 2018
Offline
SunnyKow said
Hi EhabUnable 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 .
September 26, 2018
Offline
VIP
Trusted Members
June 25, 2016
Offline
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
Answers Post
September 26, 2018
Offline
Sunny
Wow! Thank you so much. This is awesome. This will save me so much time.
Your support is greatly appreciated.
VIP
Trusted Members
June 25, 2016
Offline
Hi Ehab
You are most welcome.
Sunny
September 26, 2018
Offline
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
VIP
Trusted Members
June 25, 2016
Offline
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.
September 26, 2018
Offline
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
VIP
Trusted Members
June 25, 2016
Offline
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
1 Guest(s)
