Results 1 to 8 of 8

Thread: Populating column in another sheet only if text is matching

  1. #1

    Populating column in another sheet only if text is matching



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a large spreadsheet 'Data Source' which contains a numbered item list

    In column B, I have numbers 1-1200. Columns C-H contain information about that specific numbered item. Column I contains a category (there are only 6) call them Cat1,Cat2,Cat3...

    I want to create a spreadsheet 'Category 1'
    I want to populate column B of 'Category 1' with ONLY the items in 'Data Source' that have Cat1 in Column I

    The first item in 'Data Source' that has Cat1 in Column I is number 30, followed by number 35

    So I want column B in 'Category 1' to look like..
    B
    2: 30
    3: 35

    Column C in 'Category 1' will then use B to pull the rest of the information I need:
    =IF(B2=" "," ",VLOOKUP(B2,'Data Source'!B$4:K$1205,2,FALSE))


    I've tried a few different combinations of reference and logical formulas but can't get it to skip the items(rows) that aren't Cat1

    Anyone got any ideas?

  2. #2
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    900
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by versace View Post
    Anyone got any ideas?
    Your example file attach?
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  3. #3

    Woops

    Quote Originally Posted by navic View Post
    Your example file attach?


    Attached here
    Attached Files Attached Files

  4. #4
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    900
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by versace View Post
    VLOOKUP(B2,'Data Source'!B$4:K$1205,2,FALSE)
    VLOOKUP can't return multiple data from base. You need use INDEX/MATCH formula (known as Left Vlookup)
    ARRAY formula is next
    Code:
    =IFERROR(INDEX('Data Source'!$C$2:$K$35;SMALL(IF('Data Source'!$I$2:$I$35=MID(CELL("filename";A1);FIND("]";CELL("filename";A1))+1;256);ROW('Data Source'!$I$2:$I$35));ROW('Data Source'!1:1))-1;COLUMN('Data Source'!A1));"")
    see attach for all Cat
    Attached Files Attached Files
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  5. #5
    Quote Originally Posted by navic
    [CODE
    =IFERROR(INDEX('Data Source'!$C$2:$K$35;SMALL(IF('Data Source'!$I$2:$I$35=MID(CELL("filename";A1);FIND("]";CELL("filename";A1))+1;256);ROW('Data Source'!$I$2:$I$35));ROW('Data Source'!1:1))-1;COLUMN('Data Source'!A1));"")[/CODE]
    see attach for all Cat
    Hmm, when applying this to the larger spreadsheet it doesn't work simply by changing all the 35s to 490s

    =IFERROR(INDEX('Data Source'!$C$2:$K$490;SMALL(IF('Data Source'!$I$2:$I$490=MID(CELL("filename";A1);FIND("]";CELL("filename";A1))+1;256);ROW('Data Source'!$I$2:$I$490));ROW('Data Source'!1:1))-1;COLUMN('Data Source'!A1));"")

    When I use this, the cell goes blank

    Also: what does the 256 mean?

  6. #6
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    900
    Articles
    0
    Excel Version
    Excel 2013

    Index/Match return data from another columns

    Quote Originally Posted by versace View Post
    1. Hmm, when applying this to the larger spreadsheet it doesn't work simply by changing all the 35s to 490s.......................
    2. When I use this, the cell goes blank
    1. I've tried to get automate Sheet Name with CELL function. Given that you have a lot of rows, it appears that part of the function of MID(CELL("filename" ... volatile. See this link Sheet Name Tab in Cell (Links is on Croatian language but You can copy URL to Bing Translator)

    2. Never mind we're going in a different way.
    I have now set up a different type of formula (without MID/CELL function as condition)
    Code:
    =IFERROR(INDEX('Data Source'!$C$2:$I$12000;SMALL(IF('Data Source'!$I$2:$I$12000="Cat1";ROW('Data Source'!$I$2:$I$12000));ROW('Data Source'!1:1))-1;COLUMN('Data Source'!A1));"")
    This is ARRAY formula. You need finish formula with CTRL+SHIFT+Enter (not just enter)
    You pay attention to the part of the formula "Cat1". In this way we avoid the problematic part of the formula.
    NOTE! On each different worksheet column you need to change this part of the formula in Cat2 or Cat3 or Cat4 ...

    Of course, given that you have a lot of rows calculation takes longer. Perhaps it would be best to find a VBA macro that it could accelerate.
    See example attached, unpack this file, Excel file is too big for this forum

    Good luck
    Attached Files Attached Files
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  7. #7

    muchas gracias

    Quote Originally Posted by navic View Post

    See example attached

    Good luck

    Thanks man, will be using that for years to come

  8. #8
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    900
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by versace View Post
    Thanks man
    No problem, if you have a slow calculation, look at this example using VBA.
    On each sheet (Cat1, Cat2, Cat3 .... CAT10) you have VBA button which runs the procedure for copying.
    This is a much faster method.
    Code:
    Sub CopyCat1()
    'Cat1 is sheet name tab
        Sheets("Cat1").Select 
        Range("B1").Select
        Sheets("Data Source").Select
             ActiveSheet.Range("$B$1:$K$12000").AutoFilter Field:=8, Criteria1:="Cat1" 'filter range source data
             Range("B1:I11998").Select
               Selection.Copy
                   Sheets("Cat1").Select
                      ActiveSheet.Paste
            Range("A1").Select
        Sheets("Data Source").Select
               Application.CutCopyMode = False
                  ActiveSheet.Range("$B$1:$K$12000").AutoFilter Field:=8 'clear filter
            Range("A1").Select
        Sheets("Cat1").Select
    End Sub
    Attached Files Attached Files
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

Tags for this Thread

Posting Permissions

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