Extracting values from a list based on a count (1 Viewer)

[vangogh228]

I have event values (let's say A-Z) in column A, and counts of times which each event has occurred in column B. I want to create a table output of all the values of A-Z that have occurred exactly once... and will have a "1" beside them in column B.

I have wracked my brain on this and cannot find how to do this. Any help is greatly appreciated!

Tom

 

[RayH]

This is the Excel section.
rainman89's suggestion would work in Access.
Have you tried using Autofilter or creating a Pivot table?

Edit: Woohoo 100 posts!

 

[rainman89]

oh crap.... haah i didnt even look!!! im sorry... so used to posting in access!

 

[vangogh228]

I have an output area that will require some kind of action that would filter the list for the events for those that have occurred only once, and copied and pasted those events into another area. I can't use autofilter, because the users will not see this operational section of the sheet.

 

[niallc]

What about creating a Pivot table
where
Count is the "Page"
Event is the "Row"
Count of Event is the "Data"

then Filter the Page-"Count" for required value , in this case "1"

First ever Reply , so forgive me if in wrong format etc
niallc

 

[RayH]

A Pivot Table?
You mean like I suggested earlier?

 

[niallc]

Sorry RayH,
I don't think i have the hang of reading to the bottom of the page yet
Apologies

 

[vangogh228]

Thanks guys, but I don't want my users to have to refresh their pivottable every time the data changes. I want this to be an automatic operation, perhaps using a maro. Thanks for the help.

 

[shades]

Howdy. Using VBA you can create Pivot Tables on the fly (actually copied, pasted, and formatted appropriately, i.e. better than Pivot Tables). They are much faster, neater, and such an approach doesn't require the user to mess it up.
________
Marijuana Test

 

[RayH]

Howdy. Using VBA you can create Pivot Tables on the fly (actually copied, pasted, and formatted appropriately, i.e. better than Pivot Tables). They are much faster, neater, and such an approach doesn't require the user to mess it up.

Do you have an example of this VBA code?

 

[shades]

Not sure I should post it. I have adapted some examples from Bill Jelen (Mr Excel) and Tracy Syrstad in chapter 12 of their book VBA and Macros for MS Excel.

BTW, if you need VBA at all, this is an excellent resource. I refer to it frequently. And even little comments provide insight, shortcuts, etc. that I have not seen anywhere else.
________
Free Wordpress Themes

 

[unmarkedhelicopter]

If you can tell us where the output goes or better still give an example file, then someone can cook up some code for you.