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... |
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... |
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... |
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... |
All times are GMT +1. The time now is 07:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com