Best way to get Excel to do this?

chuckles1066 · Apr 7, 2011

I'd personally use Access for this but I've been asked to develop it in Excel.

There is to be a lookup sheet with employee names, which site they work at and various skills, training qualifications etc.

So, for example, the sheet might show Chuckles, his site is The Circus Of Horrors, his department is Children's Entertainment first aid trained would be y or n, trapeze artist grade would be 1 to 5, driving licence held would be y or n etc etc so that a complete picture in one table can be built up about every employee.

This will drive a userform or something, I imagine.

What I've been asked to do is create some sort of screen where, for example, someone can enter their requirements and it lists all qualified employees......so, above, if you entered the department as Children's Entertainment and driving licence held = y, it would bring back Chuckles (and anyone else who matched the criteria).

It's the classic select * from employees where........

Is this even do-able in Excel?

TIA.

Aladin Akyurek · Apr 7, 2011

chuckles1066 said:
I'd personally use Access for this but I've been asked to develop it in Excel.

There is to be a lookup sheet with employee names, which site they work at and various skills, training qualifications etc.

So, for example, the sheet might show Chuckles, his site is The Circus Of Horrors, his department is Children's Entertainment first aid trained would be y or n, trapeze artist grade would be 1 to 5, driving licence held would be y or n etc etc so that a complete picture in one table can be built up about every employee.

This will drive a userform or something, I imagine.

What I've been asked to do is create some sort of screen where, for example, someone can enter their requirements and it lists all qualified employees......so, above, if you entered the department as Children's Entertainment and driving licence held = y, it would bring back Chuckles (and anyone else who matched the criteria).

It's the classic select * from employees where........

Is this even do-able in Excel?

TIA.

One option is to run AutoFilter, another is to invoke a system of formulas...

Let A1:F400 on Sheet1 house such data with fields: Employee, Site, Dep, First Aid, Trapeze Artist, Driving Licence.

Sheet2

A1: Dep

A2: Children's Entertainment

B1: Driving Licence

B2: Y

A4, just enter:

Rich (BB code):

=SUMPRODUCT(--(Sheet1!C2:C400=A2),--(Sheet1!F2:F400="Y"))

A5: Employee

A6, control+shift+enter, not just enter, and copy down:

Rich (BB code):

=IF(ROWS($A$6:A6)<=$A$4,INDEX(Sheet1!$A$2:$A$400,
   SMALL(IF(Sheet1!$C$2:$C$400=$A$2,IF(Sheet1!$F$2:$F$400=$B$2,
    ROW(Sheet1!$A$2:$A$400)-ROW(Sheet1!$A$2)+1)),ROWS($A$6:A6))),"")

Peter_SSs · Apr 7, 2011

chuckles1066 said:
What I've been asked to do is create some sort of screen where, for example, someone can enter their requirements and it lists all qualified employees......so, above, if you entered the department as Children's Entertainment and driving licence held = y, it would bring back Chuckles (and anyone else who matched the criteria).

Would you only ever have one criteria (or none) per column like your example above? If so, here is one way that might suit.

Set up the sheet like this:

Excel Workbook

A

B

C

1

Name

Site

Dept

2

3

4

Name

Site

Dept

5

Name 1

Site 1

Dept A

6

Name 2

Site 2

Dept A

7

Name 3

Site 2

Dept B

8

Name 4

Site 1

Dept B

9

Name 5

Site 2

Dept A

10

Name 6

Site 3

Dept A

11

Auto Filter

Now
1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window.

 Private Sub Worksheet_Change(ByVal Target As Range) Dim LastCol As Long, c As Long Dim sCrit As String Const CritRow As Long = 2 Const TableRow As Long = 4 If Not Intersect(Target, Rows(CritRow)) Is Nothing Then LastCol = Cells(TableRow, Columns.Count).End(xlToLeft).Column With Cells(TableRow, 1).CurrentRegion For c = 1 To LastCol sCrit = Cells(CritRow, c).Value Select Case sCrit Case vbNullString .AutoFilter Field:=c Case Else .AutoFilter Field:=c, Criteria1:=sCrit End Select Next c End With End If End Sub 

Enter criteria in the yellow cells in the appropriate column. The result could be this for example.

Excel Workbook

A

B

C

1

Name

Site

Dept

2

Site 2

Dept A

3

4

Name

Site

Dept

6

Name 2

Site 2

Dept A

9

Name 5

Site 2

Dept A

11

Auto Filter

If you might want more than 1 criteria per column, how many might you want (maximum) and what version of Excel are you using?

chuckles1066 · Apr 7, 2011

Thanks both.

There will definitely be a need for more than one criteria I imagine, I think 5 would be the maximum.

My company are still using Excel XP.

Aladin Akyurek · Apr 7, 2011

chuckles1066 said:
Thanks both.

There will definitely be a need for more than one criteria I imagine, I think 5 would be the maximum.

My company are still using Excel XP.

Thanks for providing us feedback.

An efficient formula-system would be possible if a range criterion for a grade could be excluded. Otherwise, I expect VBA to be more suitable. Peter: Any comments?

Peter_SSs · Apr 7, 2011

With many columns (we don't know how many) and up to 5 criteria per column, any formula solution would be out of my reach at least.

I do have a vba suggestion, though I'm wondering if there might be a more efficient way than what I have come up with. Anyway here it is. I have done some testing in Excel 2003 so I think Excel XP should be okay but I don't have that version to test.

Set up the sheet like this:

Formula in A7 copied across.

Formula that I have in E2 needs ..
- to be in row 2 and 2 columns to the right of the last table column.
- to be adjusted to have a section like +C$7+COUNTIF(C$2:C$6,C11) for each table column.

Excel Workbook

A

B

C

D

E

1

Name

Site

Dept

2

TRUE

3

4

5

6

7

1

8

9

10

Name

Site

Dept

11

Name 1

Site 1

Dept A

12

Name 2

Site 2

Dept A

13

Name 3

Site 2

Dept B

14

Name 4

Site 1

Dept B

15

Name 5

Site 2

Dept A

16

Name 6

Site 3

Dept A

17

Auto Adv Filter

Implement this code as I described previously (remove the previous code if it exists).

 Private Sub Worksheet_Change(ByVal Target As Range) Dim LastCol As Long Const CritRow As Long = 2 Const MaxCrit As Long = 5 Const TableRow As Long = 10 LastCol = Cells(1, Columns.Count).End(xlToLeft).Column If Not Intersect(Target, Range("A2").Resize(MaxCrit, LastCol)) Is Nothing Then Range("A" & TableRow).CurrentRegion.AdvancedFilter _ Action:=xlFilterInPlace, CriteriaRange:= _ Cells(1, LastCol + 2).Resize(2), Unique:=False End If End Sub 

Then enter your Criteria in the yellow cells. Here is one example result.

Excel Workbook

A

B

C

D

E

1

Name

Site

Dept

2

Site 1

Dept A

TRUE

3

Site 2

4

5

6

7

1

0

8

9

10

Name

Site

Dept

11

Name 1

Site 1

Dept A

12

Name 2

Site 2

Dept A

15

Name 5

Site 2

Dept A

17

Auto Adv Filter

Peter_SSs · Apr 7, 2011

BTW.. If you want the sheet to look a bit tidier you could hide the formula rows/column (row 7 and column E in my example) once the formulas have been populated in them.

Aladin (or anybody else of course

)

Can you see a more efficient way to write my E2 formula? Is a SUMPRODUCT or array formula possible here?
It would be especially hand if there were say 10 or more columns.

The formula needs to return TRUE if for every column ..

either: There is nothing entered in rows 2:6 for that column

or: The value in row 11 is found in rows 2:6 in that column

Any references to the top section (rows 1-7) need to be absolute (for rows at least) and the reference to row 11 needs to be relative since the formula has to effectively apply to each of the bottom section rows from row 11 down.

Aladin Akyurek · Apr 7, 2011

Peter_SSs said:
BTW.. If you want the sheet to look a bit tidier you could hide the formula rows/column (row 7 and column E in my example) once the formulas have been populated in them.

Aladin (or anybody else of course )

Can you see a more efficient way to write my E2 formula? Is a SUMPRODUCT or array formula possible here?
It would be especially hand if there were say 10 or more columns.

The formula needs to return TRUE if for every column ..

either: There is nothing entered in rows 2:6 for that column

or: The value in row 11 is found in rows 2:6 in that column

Any references to the top section (rows 1-7) need to be absolute (for rows at least) and the reference to row 11 needs to be relative since the formula has to effectively apply to each of the bottom section rows from row 11 down.

A simpler set up maybe...

A8, copied across:

=COUNTIF(A$2:A$6,A$11)

E2 then becomes:

=SUM(A7:C7,A8:C8)=COUNTA($1:$1)

In the OP, it's said that there is a field Trapeze Artist with grades which go from 1 to 5. Does your code admit:

Trapeze Artist
2
4

as criteria?

I've avoided providing a pretty efficient formula solution which would not admit such, that is, the so called From-To criteria.

Peter_SSs · Apr 7, 2011

Aladin

Thanks for the suggestion. Unfortunately I haven't been able to get that to work with Advanced Filter. I think that the problem is that row 8 is actually static and when the Advanced Filter in the background effectively applies the E2 formula to each row, it is applying those static values from row 8 each time rather than calculating what those values would be for the row in question.

However, your suggestion did make me think differently and helped me progress as shown below in F2. This new formula would still need adjustment if the number of columns is increased/decreased, but it is now a more concise formula and therefore easier task to do the editing.

Aladin Akyurek said:
In the OP, it's said that there is a field Trapeze Artist with grades which go from 1 to 5. Does your code admit:

Trapeze Artist
2
4

as criteria?

Yes, seems to handle that fine.

Raw data:

Excel Workbook

A

B

C

D

E

F

1

Name

Site

Dept

Trapeze

2

TRUE

3

4

5

6

7

1

8

9

10

Name

Site

Dept

Trapeze

11

Name 1

Site 1

Dept A

2

12

Name 2

Site 2

Dept A

4

13

Name 3

Site 2

Dept B

2

14

Name 4

Site 1

Dept B

4

15

Name 5

Site 2

Dept A

1

16

Name 6

Site 3

Dept A

4

17

Advanced Filter 2

Filter criteria entered:

Excel Workbook

A

B

C

D

E

F

1

Name

Site

Dept

Trapeze

2

Site 2

4

FALSE

3

2

4

5

6

7

1

0

1

0

8

9

10

Name

Site

Dept

Trapeze

12

Name 2

Site 2

Dept A

4

13

Name 3

Site 2

Dept B

2

17

Advanced Filter 2

Peter_SSs · Apr 7, 2011

This would still fail, wouldn't it, if there was more than 1 field that could take similar values. For example two numeric fields that could both contain the same number?

For example here the filter shows rows where there is a 2 in either column D or column E.

Excel Workbook

A

B

C

D

E

F

G

1

Name

Site

Dept

Team

Trapeze

2

TRUE

3

4

5

6

7

1

0

1

8

9

10

Name

Site

Dept

Team

Trapeze

11

Name 1

Site 1

Dept A

1

2

12

Name 2

Site 2

Dept A

2

4

13

Name 3

Site 2

Dept B

3

2

15

Name 5

Site 2

Dept A

2

1

17

Advanced Filter 2

Back to the drawing board! .. Or back to post #6 formula.

Best way to get Excel to do this?

chuckles1066

Banned

Excel Facts

Aladin Akyurek

MrExcel MVP

Peter_SSs

MrExcel MVP, Moderator

chuckles1066

Banned

Aladin Akyurek

MrExcel MVP

Peter_SSs

MrExcel MVP, Moderator

Peter_SSs

MrExcel MVP, Moderator

Aladin Akyurek

MrExcel MVP

Peter_SSs

MrExcel MVP, Moderator

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Best way to get Excel to do this?

Banned

Excel Facts

MrExcel MVP

MrExcel MVP, Moderator

Banned

MrExcel MVP

MrExcel MVP, Moderator

MrExcel MVP, Moderator

MrExcel MVP

MrExcel MVP, Moderator

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock