+ Reply to Thread
Results 1 to 4 of 4

Very similar column - TEXT Filter on one sheet, NUMBER Filter on other - Why different?

  1. #1
    Registered User
    Join Date
    05-04-2020
    Location
    Georgia
    MS-Off Ver
    2019
    Posts
    8

    Very similar column - TEXT Filter on one sheet, NUMBER Filter on other - Why different?

    I have a workbook with two sheets. On sheet one is a single column with a header and numbers below. On the second sheet is a very similar column and some other data in the sheet. My question is about Column A in both sheets. It was pointed out to me that Column A in sheet 1 is formatted differently than Column A in sheet 2.

    Upon clicking Format Cells for each column, they indicate that they are "Number" and "General". However, when a filter is added to the columns, Column A on sheet 1 filters as "Numbers". Column A on sheet 2 filters as "Text". This was not done intentionally. Why would this happen?

    I need to use a formula to combine and align the sheets based on information in Column A of both sheets and apparently this is messing me up. How can I make both columns to be formatted the same?
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,538

    Re: Very similar column - TEXT Filter on one sheet, NUMBER Filter on other - Why different

    Where did the numbers that are text come from? You can work round the mistmatch via formulae, so no need to worry too much about it, although it would be useful to know where the text came from. Is that list the result of a formula?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    05-04-2020
    Location
    Georgia
    MS-Off Ver
    2019
    Posts
    8

    Re: Very similar column - TEXT Filter on one sheet, NUMBER Filter on other - Why different

    Hello AliGW.

    The column in sheet 1 was made by typing the first six digit "code" number and then dragging down to the logical end amount of six digit numbers, within a structure of being restrained by the first two numbers (710000 - 719999).

    The information on sheet 2 was produced (Excel report) by the program used to track this information. As you can see, the numbers in Column A in sheet 2, have breaks between the numbers. That is the difference between Column A on both sheets. That is my main purposed in this, is listing every possible number in Column A on sheet 1 and trying to bring over all the data on sheet 2, based on how they align by matching the "code" numbers on sheet 1.

    For disclosure (not breaking forum rules), I already have received help on this great forum in achieving the desired end result. But, in doing so, caused me to ask the present question of the difference in filtering. I am posting the end result spreadsheet just so you can see what I am talking about. But the formula that was very generously offered to me, had a component that I did not understand (the formula negates the filtering difference).
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,437

    Re: Very similar column - TEXT Filter on one sheet, NUMBER Filter on other - Why different

    Sheet 1

    Maybe like this.

    F2 =IF(B2="","","filter") and drag down.

    After that filter on column F and choose for "filter"

    See the attached file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

+ 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. [SOLVED] Filter specific Text from column and copy to Target sheet
    By E5254730 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-03-2020, 03:36 AM
  2. [SOLVED] Filer via 'Text Filter' not 'Number Filter'
    By jimboryan in forum Excel General
    Replies: 2
    Last Post: 05-12-2017, 03:49 PM
  3. Filter column by text and copy to another sheet
    By Grens in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-11-2016, 10:05 AM
  4. Replies: 1
    Last Post: 12-12-2014, 06:46 AM
  5. Filter Feature has Text Filters, but I want number filter
    By 6string in forum Excel Formulas & Functions
    Replies: 25
    Last Post: 06-09-2014, 02:57 PM
  6. [SOLVED] Need: filter, paste info to new sheet, rename sheet, unfilter, hide column, filter next
    By esmithqg in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-18-2012, 04:49 PM
  7. filter only one cell from various similar cells in a column
    By marvinlajiant in forum Excel General
    Replies: 2
    Last Post: 07-27-2011, 10:14 PM

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