Hi Guys,
I think this would be a simple array formula for one of you, but days of stress for me!
Workbook attached.
column 1 displays Area codes (Area 1, Area 2 .. etc)
Column 2 displays Values (Value 1, Value 2 .. etc)
I need a formula which will extract all unique values in column 2 which correspond to a specific area.
So I can identify all unique values in column 2 which correspond to Area 1, or Area 2 etc.
The workbook should make it clear.
Thanks to anyone who can assist.
F
Hi freud1,
See if the attached is good enough for you. I used a Pivot Table instead of trying to do CSE (array) formulas. With Pivots I didn't even need a formula.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Hi Marvin,
Unfortunately not, I need it to generate a list of the unique values in order to feed it into the rest of my system.
So I need a column headed "Area 1" and then underneath i need a list of unique values "Value 2, Value 4, Value 6 etc)
Thank you anyway though, I'm always impressed with a Pivot Table!
F
OK freud1 try this one.
Do you have 2007 or 2010 Excel? There are some more formula options that make this problem easier. I used 2 helper columns to solve this. See if you can see what I did.
In the .xlsx version the formula is
Please Login or Register to view this content.
Last edited by MarvinP; 06-29-2012 at 06:35 AM.
Hi Mate,
I build a macro for you and left some comments in the macro if you need to amend.
Goto sheet2 and hit the RUN Macro button. example.xls
YOu should be fine
Thanks,
Bonny Tycoon
Hi Guys,
Marvin: I got the formula working, but it is pulling duplicate values, (if you look on your example sheet under area 1 we can see Value 21 repeated 4 times??
Bonny - My Companies IT policy does not allow us to run macros so I need a Non VBA solution!
I thought this would be so simple for one of you gurus! haha
OK -
After I did the above I needed to do Advanced Filters 4 times, once for each column with the Unique Box checked.
I'm not sure there is a quick way to do this without code or pivot tables or helper columns.
Thanks for your help Marvin,
I have previously been doing it this way and running the advanced filter.
I was hoping for a non VBA solution that would automatically add new entries if the user created new Values but it seems I may have been a bit over ambitious.
Damn my IT department for banning Macros and VBA!
Thank you for your attempts, I genuinely appreciate your time.
I'll leave this flag as unsolved and the challenge is there for anyone who can find an automated non VBA way of doing this.
Thank you
F
Hi
My suggestion is using 1 helper column with an Array formula and An Array formula for the uniques.
See if this helps you.
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Hi freud1,
I took another run at this problem and have another answer. Pivot Tables are used to create unique values first. Then I build a unique key from the pivot in column I. Then using a VLookup move them to your table format. See if this works for you.. No VBA Needed!!
I'm using 2010 Excel and have attached the converted older 2003 answer too, so you can see if this method works on the older version.
Last edited by MarvinP; 07-02-2012 at 11:38 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks