Vlookup

[chrisssyp82]

Excel 2010

I've got a list of reports which give the report number, report description and the stored proc which goes with it. example below:

RPT_NUM RPT_NME SP_NME
1 Report1 SP1
2 Report2 SP2
3 Report3 SP3
4 Report4 SP1
5 Report5 SP2
6 Report6 SP1

What i would like to do is add another column and do a look up to show which reports use the same stored proc and return the RPT_NUM, i.e:

RPT_NUM RPT_NME SP_NME Equivalent Report
1 Report1 SP1 4, 6
2 Report2 SP2 5
3 Report3 SP3
4 Report4 SP1 1, 6
5 Report5 SP2 2
6 Report6 SP1 1, 4

Is this even possible in Excel?

Thanks

 

[lionelhill]

You look like you want to get multiple results back in a single row, which is a recipe for trouble.

A very simple approach which might work for you is to make a pivot table out of the data, with SP_NME as your left-hand-side category. By default, this will give you a table with one entry for each SP_NME, and the count of how many reports use this SP_NME. The nice thing, though, is that if you double click a row, it will open up a new worksheet containing a full list of all individual lines that correspond to the SP_NME on which you clicked.

 

[SkipVought]

Hi,

This is EXACTLY what a PivotTable is able to do with flying colors, in about 15 seconds!!!

If you are not familiar with PivotTables, your personal Excel Toolbox is sadly limited. It is akin to owning a Lamborghini, but pushing it to each of your destinations.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]