+ Reply to Thread
Results 1 to 32 of 32

one formula need to categorized all the items

  1. #1
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2013-365
    Posts
    500

    one formula need to categorized all the items

    Hi All,
    i need to categorized items just in one formula. attached file will show you what i need exactly. sheet1 all items with duplicate, sheet2 unique item list and sheet3 to show item category shortly not to detail with sizes. there are lot of sizes in specific items but i don't need sizes to categorized.
    can anybody tell me formula to categorized this?

    Thanks.
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: one formula need to categorized all the items

    You could do this with a pivot table similar to the attached. You don't even need the intermediate step to get unique values.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2013-365
    Posts
    500

    Re: one formula need to categorized all the items

    Hi dflak,
    Thanks to reply, i think you did not understand my question, i need to categorized item based on sheet3 criteria mention. see the snap where these items found in sheet1 and sheet2 then paste the top heading sheet3 in front of sheet2 and sheet1 items. see results in sheet2 sample. if you need more clarification please ask me.

    Thanks.
    Attached Images Attached Images

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: one formula need to categorized all the items

    Please see in the attached:

    The headers in Sheet3 have been trimmed in row 1.

    Then in Sheet1 this array entered formula in B2 filled down and across 3 columns.

    If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Array enter the same formula filled down and across in Sheet2 C2.
    Dave

  5. #5
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2013-365
    Posts
    500

    Re: one formula need to categorized all the items

    Hello FlameRetired,
    your formula is working good but there is a problem in it. some items results are different. item exist in CRHF List but result is not OK in sheet2 C column. i need only one column result column C, don't need other columns D and E. i just highlight the some items for you to consider the perfect result. i also increase sheet3 list update for more items to categorized.
    one example item T510 sheet3 exist in CRHF but result is not OK in sheet2 column C. if you can more this kindly tell me.

    please see the update file.
    Thanks for you.

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: one formula need to categorized all the items

    Please try enlarging all the new lookup range references to Sheet3 from

    Sheet3!$B$3:$D$11

    to

    the new range
    Sheet3!$B$3:$D$29

    Let know if that improves things.

    Also when there are multiple Product that are found in Item which do you want returned?

    Regarding the new lookup table Sheet3 Vascular there are Products highlighted below which are found in nearly all the Items.



    B
    2
    Vascular Products
    3
    RINT
    4
    SPL
    5
    NCEUP
    6
    NCEU
    7
    NCSP
    8
    FLCP
    9
    INT
    10
    ONYX
    11
    5
    12
    6
    13
    7
    14
    8
    15
    MCSP
    16
    EUP
    17
    FLC
    18
    ADVA

  7. #7
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2013-365
    Posts
    500

    Re: one formula need to categorized all the items

    Hi FlameRetired ,
    when i changed the range as per your instruction the first item result is not OK. please see the attached file in sheet2 c2. GWBC exist in CRHF bur formula result is Surgery. kindly check this.
    other issue i update more number include in you mention table post6.
    Thanks

  8. #8
    Forum Contributor
    Join Date
    02-09-2009
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    380

    Re: one formula need to categorized all the items

    Yeah array formulas is probably the best way to go; 7 the only way if multiple criteria I think.

    If there isn't multiple criteria could you try a 3D reference?

  9. #9
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: one formula need to categorized all the items

    Quote Originally Posted by majidsiddique View Post
    ... please see the attached file in sheet2 c2. GWBC exist in CRHF bur formula result is Surgery. kindly check this. ...
    That is because 300 and GWBC are both included in MD-GWBC3000 and the small formula in C2 is returning the smallest column number. The second smallest column number (3 in this case) could also be returned if formula is extended to the right. It is with these thoughts in mind that I extended the formula to the right. That would have included column 3 of Sheet3. It is also why I asked
    Also when there are multiple Product that are found in Item which do you want returned?

    C
    D
    2
    Surgical Products
    CRHF Products
    3
    30012
    T510
    4
    70420
    500DM
    5
    69328
    500FA
    6
    638RL
    D354
    7
    APU
    RES
    8
    071102A
    RED
    9
    LA6
    GWBC
    10
    LA7
    ENV
    11
    LA8
    EVO
    12
    61399
    EP
    13
    12010
    ATL
    14
    1000
    4598
    15
    12013
    CGRX
    16
    9124
    SES
    17
    100
    SESR
    18
    1211
    SED
    19
    200
    SEDR01
    20
    PTS
    ADD
    21
    BBP
    DDMC
    22
    300
    DTBC
    23
    4A
    DVMC
    24
    1101
    C2TR
    25
    914
    ADS
    26
    PVC
    EN1
    27
    CSG
    28
    203
    29
    2AF
    Last edited by FlameRetired; 04-12-2019 at 04:14 PM.

  10. #10
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2013-365
    Posts
    500

    Re: one formula need to categorized all the items

    Hi FlameRetired Boss,
    you are right, i got it your point after make some correction sheet3 criteria results are perfect, great boss,
    Thanks a lot. can you do it without array formula because its take some time when i apply more than 30000 rows item list. second when i delete column D and E sheet2 formula is missing show results some items why, its a part of formula, actually i don't need these extra columns and same to Sheet1 delete column C.
    Thanks again

  11. #11
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2013-365
    Posts
    500

    Re: one formula need to categorized all the items

    Hi FlameRetired
    is it possible by Power Query tricks?
    Thanks

  12. #12
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: one formula need to categorized all the items

    I do not know.

    Would you like an Administrator or Moderator to move your thread to that sub forum? Please do not attempt this yourself. That is double posting and a rules violation.

  13. #13
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2013-365
    Posts
    500

    Re: one formula need to categorized all the items

    Hi
    i do not this attempt to move this post sun forum. i don't understand your point. i just simply ask a question.

  14. #14
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2013-365
    Posts
    500

    Re: one formula need to categorized all the items

    Hi FlameRetired,
    kindly need more help you to do it sub categorized this. please see the attached file sheet3 new sub category list mention on there. old is is also available there. i remove column sheet2 column D and E formula is not working so i mention it column G and H. please don't mind.
    Thanks

  15. #15
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: one formula need to categorized all the items

    is it possible by Power Query tricks?
    I don't know. I know nothing about Power Query.

    My points were:
    Power Query and Power Pivot work together. If any sub forum specialists know the answer to your question I suspect they would. That's why I asked.
    If that happened to be what you wished I took precautions to not mislead you with that suggestion into innocently violating a rule by posting your thread there on your own. It's called double-posting and it happens often here.

    That was all.

  16. #16
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: one formula need to categorized all the items

    Regarding the latest upload.

    I am confused. This doesn't resemble what you originally asked, appears to be much more complex now and still hasn't answered my questions.

    Perhaps someone else will join in.

  17. #17
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2013-365
    Posts
    500

    Re: one formula need to categorized all the items

    Hi,
    i extremely sorry to say you that thread duplication is not my fault. thousand of people are posting there problem in this forum and post title. it is not possible to compare the duplication of thread title. if you have any good suggestion of title posting before post in this forum, kindly tell me.

  18. #18
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2013-365
    Posts
    500

    Re: one formula need to categorized all the items

    Hi FlameRetired Boss,
    new one file is same as previous the problem is i dont need column D and E but your formula is not working when i delete these columns so i explain you in column G and H od result. G is main category and H Sub category is showing.

    Thanks.

  19. #19
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: one formula need to categorized all the items

    There is currently no sub-forum for PowerQuery. The request is fine staying here.

    I will have a look and see if I can suggest a PQ solution.

    EDIT: I have looked at the latest file and can't make head nor tail of what you are trying to achieve - sorry.
    Last edited by AliGW; 04-16-2019 at 03:03 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  20. #20
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2013-365
    Posts
    500

    Re: one formula need to categorized all the items

    Hi AliGW,
    I also mention both category in my new file mention column G and H. please see again. leave it column C,D and E in sheet2 because its old.
    Thanks

  21. #21
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2013-365
    Posts
    500

    Re: one formula need to categorized all the items

    Hi,
    Any Help?

  22. #22
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: one formula need to categorized all the items

    Not from me, sorry - I still don’t get it.

  23. #23
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2013-365
    Posts
    500

    Re: one formula need to categorized all the items

    Hi all,
    please help.
    Thanks

  24. #24
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2013-365
    Posts
    500

    Re: one formula need to categorized all the items

    Hi FlameRetired ,
    you are really to close this thread. kindly help me to do sub category last file attachment mention post 20.
    Thanks

  25. #25
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: one formula need to categorized all the items

    This proposal employs three helper columns which have been moved to columns XFB:XFD for aesthetic purposes and assumes that the values in column B are correct.
    Columns XFB:XFD are populated with formulas similar to:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note that XFB1:XFD1 are populated respectively: Vascular, Surgical and CRHF
    Column C (Sub Category) is populated using: =XFB2&XFC2&XFD2
    Note that zero values are hidden using conditional formatting.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  26. #26
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2013-365
    Posts
    500

    Re: one formula need to categorized all the items

    Hi JeteMC,
    Thanks a lot to reply,
    I am little bit confuse sheet3 because if I remove the value of column D GWBC and ATL the formula is showing nothing value otherwise it is still exist in new range column K. I want to remove column B,C and D in sheet3, can I do it? Or is it a part of formula?
    I have to mention any item in both location at a time?
    Second is, Word are very important in sheet3 criteria. Example RINT and INT is very near to similar to each other but both are very different, if I delete RINT in sheet3 column B and G then formula is getting INT category ( Bare Metal Stent )so it is too different result. Short words are very important. In this case formula don’t pick any other category either near to close this. I also do filter this in sheet1 RINT result difference. If you can any other idea for this please tell me I mention there. I also mention A3 RINT just example out of range.
    All above discuss items are RED highlighted in sheet3. You can see the attached file.
    Otherwise your formula is working very perfect.
    Thanks a lot.

  27. #27
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: one formula need to categorized all the items

    As to the first point, that is why I made the statement: "...and assumes that the values in column B are correct". When I wrote the proposed formula it was with the understanding, on my part, that the process of populating Sheet1 column B had been finalized. That said it would seem a fairly simple work around would be to have Sheet3 columns B:D populate by formula based on columns G, I and K so that only one set of data has to be manually maintained.
    B3: =IF(G3="","",G3)
    C3: =IF(I3="","",I3)
    D3: =IF(K3="","",K3)
    Drag all three formulas to row 200.
    As to the second point, I'll give the matter some thought, however it seems more of a design issue than an Excel issue. It is scary to think that in the medical field such small differences in nomenclature have the potential for large differences in outcome.
    Let us know if you have any questions.

  28. #28
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2013-365
    Posts
    500

    Re: one formula need to categorized all the items

    Hi JeteMC,
    Thanks to reply,
    ok i got it your point, can you please explained me array and non-array formula, because you already solve my problem. in future i would like to add more category in case of emergency then i do it myself. it is difficult to understand the formula where i have to change it in future for more sub category 2,3,4,..... kindly tell me, in future if i want more category in sub, tell me where I have to change the formula range increase.
    Can you explained formula in some steps.
    Thanks a lot Boss,
    Thanks.

  29. #29
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: one formula need to categorized all the items

    Perhaps the article linked below will help explain array formulas. The top part of the article may not be helpful as it talks more about application to the subscription version. If you scroll down to the section with the drop downs (below the heading 'Download our examples') be sure to select "Office 2010 - Office 2019" as those would be the selections that explain how array formulas work for your (2010) version of Excel.

    https://support.office.com/en-us/art...2-ECFD5CAA57C7

    If you could upload a small sample showing a hypothetical new category and associated sub categories I'll be glad to modify the formulas to accommodate. By comparing those to the formulas in the file attached to post #25 you may be able to see how to add even more categories in the future. I'll be glad to explain anything as best I can.

    Let us know if you have any questions.

  30. #30
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2013-365
    Posts
    500

    Re: one formula need to categorized all the items

    Hi JeteMc,
    Thanks to reply,
    please see the attached file sample file to add more categorized items. the related article you send is very helpful. please see file.
    Attached Files Attached Files

  31. #31
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: one formula need to categorized all the items

    Formulas similar to those in post #27 are used to populate sheet2 columns G:I
    Formulas similar to the following are used to populate sheet1 columns I:U
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note that columns I:U may be moved and/or hidden for aesthetic purposes.
    The following formula is used to populate sheet1 columns C:G
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note that zero values are hidden using conditional formatting.
    Let us know if you have any questions.
    Attached Files Attached Files

  32. #32
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2013-365
    Posts
    500

    Re: one formula need to categorized all the items

    Great JeteMC,
    you are excellent. its cheers.
    Thank a lot Boss.
    Thanks.

+ 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. alphabetical categorized
    By BORUCH in forum Excel General
    Replies: 9
    Last Post: 04-19-2018, 03:34 AM
  2. Categorized graph with months
    By mmahrous in forum Excel Charting & Pivots
    Replies: 10
    Last Post: 10-25-2017, 11:59 AM
  3. [SOLVED] Categorized Data Validation
    By nadimqaisar in forum Excel General
    Replies: 2
    Last Post: 03-07-2017, 01:56 PM
  4. Categorized data into several class
    By drake777 in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 09-06-2016, 03:43 AM
  5. help! categorized according to number of males & females
    By James Ray in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-14-2016, 09:03 PM
  6. how to categorized accounts using excel formula?
    By katrinagidoc in forum Excel General
    Replies: 5
    Last Post: 08-28-2012, 12:04 AM
  7. [SOLVED] Customer List Categorized
    By SeaTiger in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 02-02-2006, 08:25 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