+ Reply to Thread
Results 1 to 18 of 18

categorizing data automatically to another sheet

  1. #1
    Registered User
    Join Date
    09-16-2014
    Location
    Miami
    MS-Off Ver
    2013
    Posts
    30

    categorizing data automatically to another sheet

    hi guys, thanks to all those who tried to help last time with the times...everything is working!
    here is a new problem but i think it is simple
    1. please look at sheet 2 and see that there are raw data in column f corresponding to column a(category name). so right now im just manually copying and pasting column f in sheet 2 back to sheet 1 which is not bad but im wondering if it is possible that excel does the categorizing of column a (sheet 2) and puts the data on sheet 1. i have to do hundreds of sheets so.. the hard part is that it doesnt say category for each row, it just shows blanks until the next category.

    2. this is just a nusance, if you notice f20-f41 on sheet to it doesnt say issued,final but instead says translan which causes the extraction columns (sheet 1, column b d f, h) to not work.. right now my solution is notice it manually and find and replace with (issued,final) but i m wondering if it could be written in the extraction columns or something.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: categorizing data automatically to another sheet

    You understand what you want.

    I doubt anyone else does.

    How are we matching column F sheet 2 to Sheet1?

  3. #3
    Registered User
    Join Date
    09-16-2014
    Location
    Miami
    MS-Off Ver
    2013
    Posts
    30

    Re: categorizing data automatically to another sheet

    hi there!
    sorry about that..
    in sheet 2, f is either category a, b, c, d (according to their corresponding column a). for example, f3 is category a, f4-7 is category b.
    now please look at sheet 1. what i did was copy the f46 to f84 to column a in sheet a.
    then i copied f14 to f45 to column c in sheet 1, etc. as of now im doing this by hand.

  4. #4
    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,917

    Re: categorizing data automatically to another sheet

    I did this using 2 helper columns on sheet 2.

    G2=IF(A3="",G2,A3)
    H2=G3&COUNTIF($G$3:G3,G3)
    both copied down.

    Then for the extract, use this in A2, copied down, and then copied across to the other columns...
    =IFERROR(INDEX(Sheet2!$F$3:$F$84,MATCH(Sheet3!A$1&ROW(A1),Sheet2!$H$3:$H$84,0)),"")
    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

  5. #5
    Registered User
    Join Date
    09-16-2014
    Location
    Miami
    MS-Off Ver
    2013
    Posts
    30

    Re: categorizing data automatically to another sheet

    hello, tried what you said, its not working im probably doing something wrong..
    can you look?
    Attached Files Attached Files

  6. #6
    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,917

    Re: categorizing data automatically to another sheet

    I added another sheet so you can compare the answers....
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    09-16-2014
    Location
    Miami
    MS-Off Ver
    2013
    Posts
    30

    Re: categorizing data automatically to another sheet

    works out perfect you are great!
    well there is one problem bc once i put ur formula to sheet 1.

    =IFERROR(INDEX(Sheet2!$F$3:$F$200,MATCH(Sheet1!A$1&ROW(A87),Sheet2!$H$3:$H$200,0)),"")

    i just assume there is only 200 rows at most... but sometimes it returns 0... can we get rid of that? also i was hoping to use f:f to use every row but since your wrote it starting at f3.. thats fine i guess.

  8. #8
    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,917

    Re: categorizing data automatically to another sheet

    Unless you copied this from row 87, it should be...
    =IFERROR(INDEX(Sheet2!$F$3:$F$200,MATCH(Sheet1!A$1&ROW(A1),Sheet2!$H$3:$H$200,0)),"")

    Also, ARRAY formulas (which this is, and it needs to be entered using CTRL SHIFT ENTER, not just ENTER) can become resource-heavy, so dont use entire columns - it will slow things down

  9. #9
    Registered User
    Join Date
    09-16-2014
    Location
    Miami
    MS-Off Ver
    2013
    Posts
    30

    Re: categorizing data automatically to another sheet

    yes, i did get it from row 87.

    please look at

    sheet 1: a42 and on are all 0s
    inconsistent formula in columns c, e, g

    sheet 2: column g and h after row 68

    maybe they are unavoidable due to the nature of the raw data
    Attached Files Attached Files

  10. #10
    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,917

    Re: categorizing data automatically to another sheet

    Those formulas are ARRAY formulas...
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

  11. #11
    Registered User
    Join Date
    09-16-2014
    Location
    Miami
    MS-Off Ver
    2013
    Posts
    30

    Re: categorizing data automatically to another sheet

    Hi Fb, I did what you asked but its still coming up... the 0s and the error msgs.
    Attached Files Attached Files

  12. #12
    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,917

    Re: categorizing data automatically to another sheet

    Which sheet are you talking about? When I look at sheet1, it seems to be working fine (you have no D in your sample data).

    If you meant sheet 3, some of those formulas were not array-entered

  13. #13
    Registered User
    Join Date
    09-16-2014
    Location
    Miami
    MS-Off Ver
    2013
    Posts
    30

    Re: categorizing data automatically to another sheet

    no i meant sheet 1.
    i attached the screen shot with 0s and the green error msgs.
    Attached Images Attached Images

  14. #14
    Registered User
    Join Date
    09-16-2014
    Location
    Miami
    MS-Off Ver
    2013
    Posts
    30

    Re: categorizing data automatically to another sheet

    fdibbins? you there?

  15. #15
    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,917

    Re: categorizing data automatically to another sheet

    Looking at your data on sheet1, it all looks correct.

    On sheet2, you have 40 A's, 25 B's, 1 C and no D's

    On sheet1, you have 40 A's, 25 B's, 1 C and no D's
    They are all being pulled in.

    As far as the ### in column B etc, you just need to make that column wider - double-click on the like btw B and C at the top

  16. #16
    Registered User
    Join Date
    09-16-2014
    Location
    Miami
    MS-Off Ver
    2013
    Posts
    30

    Re: categorizing data automatically to another sheet

    ...
    1) do you see the 0s in column a on sheet 1 and the green arrows on the top left corner of many cells?? thats in the screenshot
    2) and also in sheet 2 g and h from row 68 and on.. i have to check everytime to see if it fills beyond the last data entry that i paste bc if it doesnt then not all the data would be extracted to sheet 1.
    thank you.

  17. #17
    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,917

    Re: categorizing data automatically to another sheet

    OK I see the 0's in column A. Change the formulas to this...
    =IF(F3="","",IF(A3="",G2,A3))
    and
    =IF(F3="","",G3&COUNTIF($G$3:G3,G3))
    This will also take care of point 2), you can copy that down as far as you want to

    The green Triangles are excel's way of telling you there is a possible problem. In this case, it has recognised that the formulas in 1 column, are not the same as in the previous column. THis is not a big deal in this case, you can tell excel to ignore it - highlight all those cells, scroll back to the top, hover the mouse over the "starting" cell, right-click the trangle and select "ignore error"

  18. #18
    Registered User
    Join Date
    09-16-2014
    Location
    Miami
    MS-Off Ver
    2013
    Posts
    30

    Re: categorizing data automatically to another sheet

    thank you for your patience!

    here is your rep!

+ 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. help on categorizing sales data
    By piyushvarade in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 06-18-2014, 08:30 AM
  2. Categorizing a data set
    By EMWAU in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-08-2014, 08:39 PM
  3. [SOLVED] Help with categorizing my data
    By MaddyT in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 6
    Last Post: 11-23-2013, 07:25 PM
  4. [SOLVED] Categorizing data based on color, then split into columns?
    By heidilaw4 in forum Excel General
    Replies: 5
    Last Post: 05-15-2013, 10:52 PM
  5. Categorizing and sorting data
    By DA CPA in forum Excel General
    Replies: 7
    Last Post: 07-03-2009, 03:59 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