Formula to return Unique Entries Only

[hanksteph]

I am seeking to pull names from a list on one worksheet in to particular cells on another worksheet using each name only once. I need a function or series of them that can accomplish this. I have been trying to figure this out for a couple of months with no luck as I always get into some form of circular reference....Any idaes? Help!!!!

[Ron Coderre]

I attached a sample file to demonstrate the below formulas:

For a list entered into Sheet1!A2:A20, with A1 as the column heading

The below formulas are ARRAY FORMULAS, which are committed
by holding down CTRL and SHIFT when you press ENTER (instead of just ENTER):

Please Login or Register  to view this content.

Copy A3 into A4 and down through as many cells as you need to cover all possible unique items.

Does that help?
Post back if you have more questions.

[hanksteph]

I believe that it just may do the job. I simply have to combine it with the condition for triggering the action (IF) and I think I'll be in buissiness.
I intend to use it to pull names of Civil War Officers to place them in Command of units in the field in a game I am designing. I intend to use these functions to name the units and assgn Officers to them.
Thank you,
Hank

[hanksteph]

Could I get you to explain the logic used in Layman's terms so I can more easily tranlate your formula. It definatly does exactly what I need, but I will have to reverse engineer it in order to successfully use it. I believe the expalnation should help in doing so.
Thanks,
Hank

[Ron Coderre]

Starting with

Please Login or Register  to view this content.

Let's work with the core of that formula:

Please Login or Register  to view this content.

I used this variation of the INDEX(rng,rowref) function.
It starts with a vertical range of cells and returns a reference to the cell in the rowref position.
Example:
=INDEX(A2:A10,3) refers to cell A4 (the 3rd cell in A2:A10)

The MATCH function section, above, finds the first zero in an array of 1's and 0's.
That array is built by this section:

Please Login or Register  to view this content.

This part: (Sheet1!A$2:A$20<>"")
returns an array of 19 values that ultimately resolve to 1 (for non-blank cells)
and 0 (for blank cells)

The next section: ISNA(MATCH(Sheet1!A$2:A$20,A$2:A2,0))
compares each of the source cells to the cells in the list being built.
If they item is NOT in the list...MATCH returns an error...ISNA!.
The ISNA() function ultimately returns 1's for unlisted items and 0's for listed items.
Non-blank source items score a 1.
If the item is NOT already listed, 1 is subtracted from that value...resulting in 0.

These are the arrays returned by that whole section of the MATCH function

Please Login or Register  to view this content.

As you can see (hopefully) A3 indicates that the first 2 list items
are non-blank...but are already listed...so the 3rd item is listed.
Now, A4 indicates that the first 3 items are non-blank, and listed
so the 4th items is listed. etc.

Searching for a zero, the MATCH function identifies the first non-blank, unlisted item
and returns its relative position in the list.
That position is used by the INDEX function to return that item from the list.

I hope that gives you enough to work with.

[hanksteph]

I was unsuccessfull last night in implementation of the formulas (reverse engineering), but hopefully with this explanation I shall be more successful.
Thank you for your patience.
Regards,
Hank

[hanksteph]

Is there a good online source that you are aware of for doing a more detailed study of what all of the Excel Functions actually do and how to properly implement them. In other words, I am looking for a good avanced peice of material. The help feature that comes with Excel is simply not as detailed as I would wish.
Thanks,
Hank

[Ron Coderre]

Honestly, there's no substitute for experience. Most Excel classes teach
you what Excel does...but not how to use it properly. They're analogous to
showing you a 747 cockpit, pointing to each control, telling you what it does,
then giving you a pilot's license.

Here's how I recommend learning Excel "properly":

Please Login or Register  to view this content.

I hope that helps.

[hanksteph]

I have now gotten the first of the two formulas to work in my spreadsheet. However, I failed to mention that I wanted this to happen as a result of an IF statement used as a trigger mechanism. Basicly if a designated cell has a value greater than 0 (this cell is the number of men recruited to the unit), then the unit needs to be named (the names for the units are contained in the list and pulled by the method you outlined)....I seem to have a syntak problem with my formula on this. Here is my example:

=IF(F15>0,INDEX('Confed-Units'!C3:C152,MATCH(0,-ISBLANK('Confed-Units'!C3:C152),0)))


Sorry to continue on this, but thank you for your help.
Hank

[hanksteph]

I also believe I mispelled syntax.

[hanksteph]

I found my error on the IF portion and got it to function......


=IF(F15>0,INDEX('Confed-Units'!C3:C152,MATCH(0,-ISBLANK('Confed-Units'!C3:C152),0)))


Worked prefectly....On to the 2nd formula now....Thanks again.

[hanksteph]

I have tried 2 different versions.....both are not counting to the next unit name. They both name all the cells the same name (1st Alabama).

=IF(F16>0,IF(COUNT(MATCH('Confed-Units'!C$3:C$152,C$3:C3,0))<COUNT(1/('Confed-Units'!C$3:C$152<>"")),INDEX('Confed-Units'!C$3:C$152,MATCH(0,('Confed-Units'!C$3:C$152<>"")-ISNA(MATCH('Confed-Units'!C$3:C$152,C$3:C3,0)),0)),""))


and


=IF(F16>0,IF(COUNT(MATCH('Confed-Units'!C$3:C$152,C$3:C3,0))<COUNT(1/('Confed-Units'!C$3:C$152<>"")),INDEX('Confed-Units'!C$3:C$152,MATCH(0,('Confed-Units'!C$3:C$152<>"")-ISNA(MATCH('Confed-Units'!C$3:C$152,C$3:C3,0)),0)),""))


My list has units named from the 1st Alabama through the 150th Alabama.

OK, now oddly I am getting it to give me the 1st Alabama twice and then count perfectly.....I am experimenting with solutions.
Regards, Hank

[hanksteph]

I now have it naming units....I found the issue that was keeping it from counting.....Now I have to figure out exactly how to use the relative funtions to make it name them exactly as I wish. Regards, Hank

[hanksteph]

I would like to know how to make the formula always pick the next name and not skip places with the row number....Here is the portion of the sheet I am working on:


Begin
L 2 1
U 500 1 I M 1 8 1st Alabama
U 500 1 I M 1 8 2nd Alabama
U 500 1 I M 1 8 3rd Alabama
U 500 1 I M 1 8 4th Alabama
U 4 2 A K 32 12
End

Begin
L 2 1
U 500 1 I M 1 8 10th Alabama
U 500 1 I M 1 8 11th Alabama
U 422 1 I M 1 8 12th Alabama
U 0 1 I M 1 8 FALSE
U 2 2 A K 32 12
End


The 10th Alabama should be named the 5th Alabama instead if as intended.
Thanks, Hank

[hanksteph]

I am learning a lot here. Unfortunaly, I have not been able to get the units to be named in sequence. They are being named in reference to what row they are on, causing the gap in the names from 4th Alabama all the way to 10th Alabama when they should have been consecutive.
Thank in advance,
Hank

[Ron Coderre]

I think it's time for you to post a sample of your data and the formulas you're working with.

[hanksteph]

I shall do so this evening when I once again have access to the latest version of the spreadsheet. It is 8:15 AM here, and I will not be able to reply until about 7PM. Thank you for your efforts,
Hank

[hanksteph]

OK, here is the file....the sheet in question is the Confederate1-61 page cells L15 and 16 and continueing on down the sheet....I will also be using these formulas to pull leader names. If this has not uploaded then I shall need some instructions on that.....I think I did it right, but I do not see the attachment.
Regards, Hank

[hanksteph]

The file is over 2mb......I will try to cut out much of it and post a slimmed down version tonight.
Hank

[NBVC]

Note that this forum accepts Zipped files too.. that may reduce it to under 1mb

[hanksteph]

It does occur to me that perhaps an IF statement I added to the formula as a precondition could be cuasing problems.....Could you take a look at this formula and tell me if this would cause issues?


=IF(F16>0,IF(COUNT(MATCH('Confed-Units'!C$3:C$152,C$3:C4,0))<COUNT(1/('Confed-Units'!C$3:C$152<>"")),INDEX('Confed-Units'!C$3:C$152,MATCH(0,('Confed-Units'!C$3:C$152<>"")-ISNA(MATCH('Confed-Units'!C$3:C$152,C$3:C4,0)),0)),""))


Have I made an error in the fashion I added =IFF16>0 to the formula?

I still intend to get the file to this board but have not been able to do so yet.
Regards,
Hank

[hanksteph]

OK,
I finally got the parts of the spreadsheet I needed into a small enough file....Here are the issues, L16 did not count (should have been 2nd Alabama).........L24 should have been the 5th Alabama.....I was able to fix these issues by presetting some of the cell references in the formulas, but this will not do as the numbers in the cells that the manpower is drawn from are determined by many factors contained on yet another worksheet. Bottom line is the troops recuited and represented here are being raised in the city of Decatuer Alabama (Cell D2). If you scroll furrther down the worksheet you will see that Mobile Alabama did not recruit any troops this turn (Cell D62), and then further down yet in Cell D 122 Montgomery Alabama did recruit. Point being, I do not know what cities will recruit on a given turn, therefore the formula must take into consideration the fact that the names could have been pulled to any of the cities. For example, had Decatuer Alabama NOT recruited then the 1st Alabama would have been raised in Montgomery Alabama (Cell L135)...BTW The numbers of men in each "Regiment" the 500s follow by 422 in column F are pulled by formula from Cell D2.
I hope this gives you some idea of the issues I am facing here....I truly appreciate the help you have so far given. I think we are close to a solution.
Thanks,
Hank

[hanksteph]

I have realized that since I do not know which cell will be getting the first name on the list, then the first formula which only returns the first name is of no use....I need to use a version of the 2nd formula for all of the cells.....
Thanks for your help,
Hank

[hanksteph]

Seems so far, nobody has a final answer here......Would I be better off going to VB to program Excel to accomplish this?
Regards, Hank

[Ron Coderre]

Sorry about the delay....
Your formulas weren't referencing the list you were building in Col_L.
They were referencing Col_C.

Try this ARRAY FORMULA on the Confederate1-6100B sheet

Please Login or Register  to view this content.

Copy that formula into L17:L19 and into L24:L28

Does that help?

[hanksteph]

It pulled up 1st Alabama the 1st 6 cells and 2nd Alabama in the 7th and 8th cells.....I am sure I likely did something very wrong....but I can't find the mistake.....Any chance I can email you the file or give you an addy where you can download it so you can get a look at the complete file?
Regards, Hank

[Ron Coderre]

Using your posted file...on the Confederate1-61OOB sheet...

Please Login or Register  to view this content.

NOTE: That is an ARRAY FORMULA, committed with CTRL SHIFT ENTER.

Copy L16 into L17:L19 and into L24:L28

These are the returned values:

Please Login or Register  to view this content.

I hope that helps.

[hanksteph]

I inserted another column in the units worksheet which moved the proper column to D instead of C and the first formula in cell L15 should have started with=IF(F15>0....however I realise those are not the problems.....I have obviously made an error that is preventing the formula from moving to the next cell, and I do not understand what it is....LOL....I guess at this point I am getting a bit frustrated....Oh well, at least there is no danger of my ego getting out of control.....This last attempt I made, every cell returned 1st Alabama (which should have been in Cell 3 of the list column on the units worksheet on the sample you recieved, but was not.....
I thank you for your efforts
Hank

[hanksteph]

When I copied your formula directly into my sheet and simply changed the column from C to D to correct for the inserted row, and changed the F-16 to F-15 in the begining IF statyement, I then get a circular reference and a result of O. Attached is a screen shot....Perhaps you can see the error in the formula.....I have been unable to do so. OOPs....once again too big for uploading.....So......



=IF(F15>0,IF(COUNT(MATCH('Confed-Units'!D$3:D$152,L$15:L15,0))<COUNT(1/('Confed-Units'!D$3:D$152<>"")),INDEX('Confed-Units'!D$3:D$152,MATCH(0,('Confed-Units'!D$3:D$152<>"")-ISNA(MATCH('Confed-Units'!D$3:D$152,L$15:L15,0)),0)),""))


Here is my version of the formula copied from cell L15.

Regards, Hank

[hanksteph]

I finally moved the formulas in your example to column B on your sheet by inserting a column and that greatly clarified how the functions operated for me. I was then able to get them to work in my sheets. Having said that, I may have to rethink my methods. The worksheet that feeds the sheet the formulas are for operates off of conditional random functions, so not even I have a way of know what the results will be on any given turn. I may be able to solve this with a Macro to convert these results into what I need and set up for the next turn.
One last question, I run the fist worksheet, then lock it down where it cannot claculate using the visual basic editor. The shift-control-enter action seems to have overidden that lockdown.....should this have occured? It may have been a result of tranferring the file from computer to computyer via memory stick.
Thanks for your efforts,
Hank

[hanksteph]

Ron,
I have the spreadsheet pulling the unit names of Infantry, artillery, and cavalry flawlessly at this point.....I finally stumbled around enough to get it....Thank you so much....I have a LOT of work to do to get all the formulas inserted in the proper cells because the sheet is so massive, but I will need (likely) one more bit of help. When I puul the Officer's names from those list's I may need to have it pulls several colums from the row the leader is on.....I'll ask that question when I get to it I suppose....I may not need to as I think I know how to do that.......I do wish to sincerely thank you....I suspect I was a difficult pupil.....Many thanks, Hank