Populating column in another sheet only if text is matching

V

versace

New member
Joined
Oct 16, 2015
Messages
8
Reaction score
0
Points
0
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?
 
versace said:
VLOOKUP(B2,'Data Source'!B$4:K$1205,2,FALSE)
Click to expand...
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
 

Attachments

  • versace-navic.xlsx
    40.6 KB · Views: 19
navic [CODE said:
=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
Click to expand...

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?
 
Index/Match return data from another columns

versace said:
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
Click to expand...
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
 

Attachments

  • versace-ExampleSpread.zip
    333.9 KB · Views: 18
versace said:
Thanks man
Click to expand...
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
 

Attachments

  • versace-ExampleSpread-VBA.zip
    452.7 KB · Views: 16
You must log in or register to reply here.
Back
Top