Lookup and return range of values

[Jose Mourinho]

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...

 

[Luke M]

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.

 

[Gary''s Student]

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.

 

[Ashish Mathur]

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