Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup and return range of values
I have Sheet1 laid out as follows:
Month CatA CatD CatJ Month 1 10 50 10 Month 2 5 10 5 Month 3 5 10 10 I need to divide data into 1 sheet per Category (Cat) data i.e. Month CatA Month 1 10 Month 2 5 Month 3 5 Huge sheet, many categories, finding HLOOKUP ("CatA", Sheet1!RefTable, x) very cumbersome. There must be a simpler way... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup and return range of values
It appears you are just transferring data from one place to another. Perhaps:
=INDEX('Sheet1'!$B$2:$D$4,MATCH($A2,'Sheet1'!$A$2: $A$4),MATCH(B$1,'Sheet1'!$B$1:$D$1)) or something similar will work. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Jose Mourinho" wrote: I have Sheet1 laid out as follows: Month CatA CatD CatJ Month 1 10 50 10 Month 2 5 10 5 Month 3 5 10 10 I need to divide data into 1 sheet per Category (Cat) data i.e. Month CatA Month 1 10 Month 2 5 Month 3 5 Huge sheet, many categories, finding HLOOKUP ("CatA", Sheet1!RefTable, x) very cumbersome. There must be a simpler way... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup and return range of values
First make sure that Sheet1 is the first (left-most) sheet and that there are
plenty of blank sheets following it. Then run this small macro: Sub splitdata() n = 4 For i = 2 To n Sheets(1).Range("A:A").Copy Sheets(i).Range("A:A") Sheets(1).Columns(i).Copy Sheets(i).Range("B:B") Next End Sub I used 4 because you had 4 columns in your example. Adjust to match your actual data. -- Gary''s Student - gsnu200836 "Jose Mourinho" wrote: I have Sheet1 laid out as follows: Month CatA CatD CatJ Month 1 10 50 10 Month 2 5 10 5 Month 3 5 10 10 I need to divide data into 1 sheet per Category (Cat) data i.e. Month CatA Month 1 10 Month 2 5 Month 3 5 Huge sheet, many categories, finding HLOOKUP ("CatA", Sheet1!RefTable, x) very cumbersome. There must be a simpler way... |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup and return range of values
Hi,
Please follow the undermentioned steps: 1. Highlight the range and go to Data Pivot Table and Pivot Chart Multiple Consolidation Ranges Ok 2. Let the default remain on the next screen; 3. Highlight the range to be pivoted and say OK Now in the pivot table which emerges, drag out row and column fields so that you see only one number inside the pivot table. Double click the number and data will be organised by rows in another sheet. Now create a simple pivot of this with column field in the page area. Now while in the pivot table, click on Show Pages in the pivot table in the toolbar and you will notice that you will get one sheet per category -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Jose Mourinho" wrote in message ... I have Sheet1 laid out as follows: Month CatA CatD CatJ Month 1 10 50 10 Month 2 5 10 5 Month 3 5 10 10 I need to divide data into 1 sheet per Category (Cat) data i.e. Month CatA Month 1 10 Month 2 5 Month 3 5 Huge sheet, many categories, finding HLOOKUP ("CatA", Sheet1!RefTable, x) very cumbersome. There must be a simpler way... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup 3 matching values of which one is a range and return a sum | Excel Worksheet Functions | |||
lookup single value in a range and return specified value | Excel Worksheet Functions | |||
How do I use LOOKUP to return a range of values, then SUM values? | Excel Worksheet Functions | |||
how to lookup a value within a range and return a label | Excel Discussion (Misc queries) | |||
How do I lookup and return different values when the lookup value. | Excel Discussion (Misc queries) |