Hi guys! I have an excel sheet with multiple columns containing list of firms . Please I need help on how to identify only firms that reoccurred in each of the columns and delete firms that can't be found in each of the multiple columns. Please I need help on how to do this
Good afternoon p99,
I have a few quick questions to help better understand what you're looking to accomplish.
When you say delete firms that can't be found in each of the columns, do you mean the firms should appear in all of the following columns in order to remain (A, C, E, G, I)?
In the same thought train, would row two be deleted because SOUTH.AFN.COAL.MNG is not present in column I?
Good day Chenderson,
Thanks for your reply. Yea,i mean the firms should appear in all the columns(A,C,E,G,I) irrespective of there row positions, hence SOUTH.AFN.COAL.MNG in row two will be deleted because it only appeared in column(A,C,E,G).
Please the main data has more than 200columns and 5000rows,but the same format is maintained just as the attached sheet.
One option is to enter the following formula in cell K2 and fill downwards :
=COUNTIF(A2:I2,A2)=5
Anther option is :
=AND(A2=C2,C2=E2,E2=G2,G2=I2)
Last edited by Chenderson; 08-11-2016 at 07:25 PM.
You could use Conditional Formatting to identify those entries that have 5 (or more). You say that you have 200 columns and 5000 rows. This can be adjusted to accommodate your data.
Select the data and enter the following formula in Conditional Formatting, New, Use Formula...
Formula:
Please Login or Register to view this content.
This will highlight all entries with the formatting that you choose that have 5 entries, you can delete the rest.
I used red to show the items to keep and have added filters that you can use to sort by colour.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Thanks guys, the last function gave me exactly what I desired
Thank you for the feedback.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks