Not quite sure about the best way to process this.
If I have four columns of names:
A - B - C - D
Each list is of a different length
I'd like to have a 5th column which provides a list of the names which appear in all 4 columns
And then a 6th column which provides a list of the names which appear in 3 out of 4 columns.
Is there an easy way to 'programme' those 5th and 6th columns to produce that desired effect?
Thanks so much
1. Are the names unique in each column? They don't repeat?
2. Is it easy to make a list of ALL the unique names in one column or are there too many of them to do that?
Last edited by Special-K; 03-07-2019 at 09:04 AM.
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
Assuming the data consists of a, b, c, d, e and is in A1:D5 and the names are unique in each column and are list of differing lengths.
Make a list of the unique names in column G (in this case a,b,c,d,e)
in H1
=COUNTIF(A$1:D$5,G1)
Column H will give you a count against each name in column G.
Not quite the format youre asking for I know
Last edited by Special-K; 03-07-2019 at 09:15 AM.
Or here's a different way with the format you want
Again make a list of the unique names in column G
in column H
=IFERROR(INDEX($A$1:$D$5,AGGREGATE(15,6,ROW($A$1:$D$5)/(COUNTIF($A$1:$D$5,G$1:G$5)=4),ROWS(A$1:A1))-(1-1),1),"")
and copy down as far as row 5.
in column I
=IFERROR(INDEX($A$1:$D$5,AGGREGATE(15,6,ROW($A$1:$D$5)/(COUNTIF($A$1:$D$5,G$1:G$5)=3),ROWS(A$1:A1))-(1-1),1),"")
and copy this down as far as row 5.
Further to Special-K's answers, here's how to get the list of unique names in column G.
With your data starting on row 2 (that's important - if it starts on row 1 this won't work) and going down to row 5, put this in G2:
Formula:
Please Login or Register to view this content.
Important: this is an array formula so needs to be entered using Ctrl-Shift-Enter not just Enter. You will know the array is active when you see curly brackets { } appear around your formula - don't try to enter them yourself. If you do not Ctrl-Shift-Enter an array formula you will get an error or a clearly incorrect result. If that happens, just click into the formula bar and try again.
Drag it down until you start getting zeroes. You can get rid of the zeroes by adding &"" to the end of the formula:
Formula:Please Login or Register to view this content.
Now you'll get blanks after the list of names.
Hope that helps - combined with the answers above.
Edit: by the way, when I say 'unique' above, what I actually mean is 'distinct'. 'Unique' means 'values which appear once and once only' whilst 'distinct' means 'all values which appear at least once' - in other words, distinct includes values which appear twice or more, whilst unique would exclude any values which appear more than once.
Last edited by Aardigspook; 03-08-2019 at 11:22 AM. Reason: 1. Add emphasis / 2. note about distinct vs unique
Regards,
Aardigspook
I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
You don't need to give me rep if I helped, but a thank-you is nice.
Thank you everyone for your replies.
I'm still trying to work this into my 'real world' example, but looks like I have the correct solution now. Thank you all
Hi Both,
SO I have the Array formula working nicely to produce the unique list of names in col G - Thanks Aardigspook.
But reworking the formula from SpecialK for column H and I doesn't appear to be working.
I have my lists of names in columns A, B, C and D.
The first row, under the head is row 4.
The formula in cell H4 (then copied down) is:
=IFERROR(INDEX($A$4:$D$100,AGGREGATE(15,6,ROW($A$4:$D$100)/(COUNTIF($A$4:$D$100,$G$4:$G$100)=4),ROWS($A$4:A4))-(1-1),1),"")
This produces a 0 in cell H4 and blank cells in the rest of that column.
Have I edited the formula incorrectly? Is it to do with the AGGREGATE ??
Thanks
See if the following produces the expected results:Formula:Please Login or Register to view this content.
If this doesn't work then I suggest uploading a small fictionalized sample of the data along with expected results (manually typed) so that we can test ways to replicate the results using formula(s)/code.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Hi JeteMC and all.
I've had a play.
This looked really good and is so almost there. I integrated this well and all seemed to work UNTIL you get beyond a few names in the list at which point it stops working and I cant work out why.
I think its a really interested challenge which works well as a list comparison tool.
I've attached an example using fake names.
I'd love the two columns to work properly all the way down.
One change I made was to make it >=4 rather than just =4.
One further thought I had was whether the "4" above could actually be a cell reference = ">=J2". J2 could then be the number 4 or 3 and this would change how many names were displayed. BUT I couldn't make that work at all.
Any ideas? Its a fun little challenge and I really really appreciate your input.
On the processing sheet try the following:
1. Type the number 3 into cell H1
2. Type the number 4 into cell I1
3. Paste the following formula into H4:Formula:Please Login or Register to view this content.
4. Drag the fill handle over to I4
5. While H4:I4 are still selected double click the fill handle of cell I4.
Let us know if you have any questions.
Perfect, that cracked it. Thank you.
I'm uploading the final spreadsheet in case it is useful to anyone else. Its a good little comparison tool for looking up items that are features multiple times in a data set - a comparison tool.
Thanks to all those who contributed.
Keep on Spreadsheeting
You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.
Glad we could all help and thanks for the rep.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks