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?
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
VLOOKUP can't return multiple data from base. You need use INDEX/MATCH formula (known as Left Vlookup)
ARRAY formula is next
see attach for all CatCode:=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));"")
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
Hmm, when applying this to the larger spreadsheet it doesn't work simply by changing all the 35s to 490sOriginally Posted by navic
[CODE
=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?
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)
This is ARRAY formula. You need finish formula with CTRL+SHIFT+Enter (not just enter)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));"")
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
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
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
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
Bookmarks