Excel macro problem

[DaveB]

Need some help from those of you more familiar with VB and macro's than I am.

I have an excel work book. In the first sheet I have a list of user ID's and function categories thay have access to in an ERP application.

Sheet 1 :

user ID  Category
1          cat1
1          cat2
1          cat3
2          cat4
2          cat5
2          cat6

.... etc.

In the second sheet I have a list of Funtion categories and the actual funtions associated with each category.

Sheet 2

Category     Function
cat1           func1
cat1           func2
cat1           func3
cat2           func1
cat2           func2
cat2           func3
cat3           func1
cat3           func2
cat3           func3

etc....

This is highly simplified. There are dozens of categories and thousands of funtions involved.

I can filter the 1st sheet by user and get a list of function categories they have access to. What I would like to do is be able to filter the second sheet based on the categories returned by the filter in the first sheet and display the results on the first sheet, ideally by selecting a category from the list returned

So the macro should do somthing like

select all FUNCTIONS from sheet 2 where CATEGORY = value of cell selected in sheet 1.

Is this possible? Am I making sense?

 

[r0bly0ns]

It's years since I've done anything like this (back in my permie days), but I'm sure you could do what you want with a Pivot table.

We used to have a spreadsheet containg helpdesk calls, resolutions, esculations etc..., all on seperate sheets.

We then used a pivot table to show a summary to the client.

I can't actually remeber how to do it, but I think the Excel pivot table help should point you in the right direction....

 

[MrRobin]

Do you have to use Excel for this? It would be a whole lot easier if you could chuck the contents of the 2 sheets into 2 tables in Access and then write a query for it...

 

[Sockpuppet]

Yes, its possible but maybe not filter as AFAIK that allows 2 criteria.

If it was me I'd upload it to Access and SQL it.

 

[BoredBloke]

are you trying to have 2 list boxes - the contents of the second depending on the value in the first? If so I have something which you might find useful - in Excel

Send me a pm with youe mail in it and I'll send it you

I nicked it from http://www.brainbell.com/tutorials/...ge_Based_On_A_Selection_From_Another_List.htm

as I had a similar problem a while ago. The example in the page above doesn't quite work but I managed to fix it.

 

[DaveB]

Thanks for the replies guys, Access isn't an option unfortunately. Client doesnt want to buy a license just for this job

Tony, I've PM'd you.

 

[MrRobin]

Your client doesn't have Access licences??!! Are they using Office Home edition or something??

Anyway, if TonyEnglish's solution isn;t any good for you, I think this might actually be possible without using any VB, just some clever functions like OFFSET and FIND etc... So long as your data is sorted in order

 

[EternalOptimist]

build a third column

=b1 & a1

this will concatenate the two fields then sort.
all your func1 will be grouped together


a pooey solution I agree

 

[Pondlife]

If the used rows in sheet1 x used rows in sheet2 < 65K then populate sheet 3 with the following and filter on col1

Sub fill_data()
lastrec = 1
i = 2
 Do Until i = Sheet1.UsedRange.Rows.Count + 1
  usrid = Sheet1.Cells(i, 1).Value
  catid = Sheet1.Cells(i, 2).Value
   j = 2
   Do Until j = Sheet2.UsedRange.Rows.Count + 1
    catid2 = Sheet2.Cells(j, 1).Value
    functid = Sheet2.Cells(j, 2).Value
    If catid = catid2 And functid <> "" Then
     Sheet3.Cells(lastrec, 1).Value = usrid
     Sheet3.Cells(lastrec, 2).Value = catid
     Sheet3.Cells(lastrec, 3).Value = functid
     lastrec = lastrec + 1
    End If
   j = j + 1
   
   Loop
   
   i = i + 1
  Loop
End Sub

 

[DaveB]

Pondlife, that would be spot on but for the fact that the two sheets contain 12746 and 15115 records respectivly. If I restict the number of rows read, rather than letting it try and go to the end it works just fine.

With a bit of jiggery pokery I think I can make it work by doing it by user ID from the 1st sheet and searching for the matching categories in the second sheet then creating a new sheet for each user ID ( 207 in total ). Back to the VB books.