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

1. ## Populating column in another sheet only if text is matching

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. Originally Posted by versace
Anyone got any ideas?

3. ## Woops

Originally Posted by navic

Attached here

4. Originally Posted by versace
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

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

Originally Posted by versace
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

7. ## muchas gracias

Originally Posted by navic

See example attached

Good luck

Thanks man, will be using that for years to come

8. Originally Posted by versace
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```