Condense specific matrix data to seperate list

[DutchKevin]

Hello friends of excel,

At work we have a sheet with data in ± 70 columns by 9 rows.
In each cell a letter from A-E can be there, I do this through a nice lookup, but that's not really the issue.

I'm looking for a way in which I can easily create a (dynamic) list on a second sheet to display for all occurances of, for example E, the relative position in the matrix.

I will then process the created list to a seperate action log.
In this way i would like to keep track of new or deleted "E"s.

Should the amount or position of E's change I will be notified through a conditional check against the actionlog. That part I can manage, I'm stuck on the step before that, listing the E's.


Summed up:
If there are 25 E's in sheet 1
There will be a list of 25 on sheet 2
Each row contains the row and column number of that E
Sheet 2 will follow all updates in sheet 1

Hope you like my challange, i look forward to your ideas.
PS: I'm also open to VBA sollutions.

Thanks
Kevin

 

[Datsmart]

I would suggest using Advanced Filtering.
Good example of Advanced Filter steps here:
http://www.contextures.com/xladvfilter01.html

AdvancedFilter Video Demo here:
http://www.contextures.com/xlVideos04.html
http://www.contextures.com/xlVideos04.html#AdvFilt2007
Another good link with sample VBA code here:
http://www.meadinkent.co.uk/xlfilter.htm

Advanced Filter allows you to preset filter options.
It also allows you to filter by criteria in ways standard autofilter can not do. You can choose an export location in the dialog box or in VBA code.
Once you setup the code and criteria, you can use a macro button to copy specific data from your main sheet to a "Report" sheet. The original data remains unchanged and the data "Filtered" to another page can be used as desired.

 

[DutchKevin]

Hi John,

Thanks for your suggestion, and sorry i'm a bit late in my reply.
Through your suggestion I've learned another new thing about the Advanced filter. I did not knwo it was such a nice tool.

However for my case I now use a different approach with conditional formats after which teh user must choose to add or delete items from the report list.

Regards
Kevin

 

[Datsmart]

Glad you figured something out that works for you.

That is what is great about forums like this, an exchange of ideas to help each other improve skills to get a job done.
Thanks for the update.