+ Reply to Thread
Results 1 to 2 of 2

Need a checker cell to look at the first 4 letters in each cell in a range for duplicates

  1. #1
    Registered User
    Join Date
    08-26-2022
    Location
    Houston
    MS-Off Ver
    2019
    Posts
    1

    Need a checker cell to look at the first 4 letters in each cell in a range for duplicates

    I'm looking to create multiple rosters for events. I'm looking to have one cell that looks at a range of cells and checks to see if any of the range's cell values are duplicates with the same first four letters. I have a base CountIf that I'm building off of, which works to look at one cell's first 4 letters compared to a range. This is close but I want one cell that looks at the whole range for duplicate first 4. It's meant to be a checker cell that indicates I've put the same person down for a roster twice.

    =IF(COUNTIF(A:A,LEFT(A2,4)&"*")>1,"Duplicate","")

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Need a checker cell to look at the first 4 letters in each cell in a range for duplica

    This will count how many times the 1st 4 letters are repeated (even if it not always teh same 4 letters...
    =SUM(SUMPRODUCT(--(LEFT(Q2:Q7,4)=LEFT($Q$2:$Q$7,4))))

    Excel 2016 (Windows) 64 bit
    Q
    2
    aaaa123
    3
    aaaa234
    4
    aaaa235
    5
    aaab123
    6
    aaab223
    7
    aaab223
    Sheet: Sheet2
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Count the number of letters in a range of cell
    By joe99153 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-09-2015, 07:44 AM
  2. [SOLVED] Search CELL-A for one or two letters and display the found letters in CELL-B
    By adamtre in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-03-2014, 02:43 AM
  3. [SOLVED] Duplicates checker - with messagebox listing duplicate results
    By Gti182 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-18-2014, 05:52 AM
  4. Cell Input checker
    By stevewilde in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-10-2009, 11:23 AM
  5. [SOLVED] how do I set letters to indicate a range of values in one cell
    By BritArchr in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-23-2006, 08:10 PM
  6. Cell phone tariff checker design help
    By Gareth Trow in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-21-2005, 10:05 AM
  7. Cell phone tariff checker design help
    By Gareth Trow in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-20-2005, 08:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1