LinkBack URL
About LinkBacks
I am trying to produce a list of directors from a data set as shown in the example. I have tried using a pivot table but the blank cells become unmanageable. A single (or multiple) formulas would be my preferred solution.
Jim O
I am trying to produce a list of directors from a data set as shown in the example. I have tried using a pivot table but the blank cells become unmanageable. A single (or multiple) formulas would be my preferred solution.
Jim O
Last edited by JO505; 11-18-2015 at 07:17 PM.
please add the expected result in your file (manualy).
Please Login or Register to view this content.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
See column "I". Its just a list of each individual director.
Jim O
Sorry that is the wrong file. I will upload the correct one.
Jim O
This should work better. Sorry.
Jim O
copy column B, C, D and E under eachother (in e.g. column L).
after that remove duplicaties => data => remove duplicate
after that sort on column L from A - Z
I was looking for a dynamic formula so as the data updates the single list will do likewise.
Jim O
I have found a formula that gets me close to what I am looking for (column 'J') but I need to reduce the data from the blank cells (0's).
In an ideal world it would produce a unique list but just reducing the 0's would help.
Here is a link to the formula.
Jim OHTML Code:
Example Single Col from 2 Lists-1.xlsx
See my attached formula model. Create 2 named ranges. . .here, they're called list1 and list2. See if it works for you. Blanks are removed. Enter as array formula.
Pete
Last edited by PeteABC123; 11-17-2015 at 05:14 PM.
For some reason I am having an issue getting third column into the formula.
Jim O
Keep plugging away. I'll keep trying as well.
Pete
Enter this in F2 and fill down the length of the data to identify unique values in column B and give a value that will be used to order the unique values.
Formula:
Please Login or Register to view this content.
Enter this in H2 and fill down to get the values from column B in order.
Formula:Please Login or Register to view this content.
I don't follow what you are doing with the other columns.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
I got this formula from here:
http://www.get-digital-help.com/2009...umns-in-excel/
It's the second formula on that page above and takes care of the blank cells.
Array entered in J2 and filled down it takes care of all 4 columns.If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.Formula:Please Login or Register to view this content.
Dave
FlameRetired,
Thank you for the link. That seems to be just what I needed. Also Thanks to all who provided input and suggestions on this topic.
Thanks again
Jim O
You are welcome. Thank you for the feedback.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks