Hi All
Could someone please help me understand what im missing or doing wrong in my formula please, I believe i am close to getting it to do what i want it to do but cant quite grasp it.
I want to make a unique list from my data, and i did manage to do it, until i put another "category" column in and merged the sections, but i need to have these extra two things in my sheet
Now i have them in my sheet it just returns the same value all the time.
Any help would be great
Thank you
Dan
I've uploaded one way to create a unique list.
It uses VBA so don't know whether it is suitable for you
Hope it helps!
Strud
Out of curiosity how to see code you wrote?Sorry I don't know much about VBA. Thanks!Originally Posted by strud
When in the spreadsheet hit Alt + F11 and select the moduleOut of curiosity how to see code you wrote?Sorry I don't know much about VBA. Thanks!
Hi,
Not quite sure I understand. All the entries in column A are "Orange" - are you saying that this may not always be the case? And that you wish to return a unique list of column B entries whose corresponding column A entry is "Orange"?
Regards
Hi
Yeah VBA is fine, i can work with that, my aim is a summary page of a large amount of data, with duplicate parts, if i take your example and use the drop down, but limit the drop down in the sections of my report to 1 part only it will work,
I can get my formula to work on 1 condition being the part, but couldnt combine the category to it to work, so i appreciate your help thanksi have been searching the web for a solution for hours now, and cant find anything other than using the advanced filter option, but thats not ideal as it requires the user to be able to do this.... Using the generate button is a much better way, thank you
Dan
If you did want a formula-based solution, and the answer to my previous question was yes, then, in E5, this array formula**:
=IFERROR(INDEX($B$5:$B$27,SMALL(IF(FREQUENCY(IF($A$5:$A$27=$D$5,MATCH($B$5:$B$27,$B$5:$B$27,0)),MATCH($B$5:$B$27,$B$5:$B$27,0)),ROW($A$5:$A$27)-MIN(ROW($A$5:$A$27))+1),ROWS($1:1))),"")
Copy down until you start to get blanks for the results.
Regards
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
XOR LX, yes that is the case somethimes it will be different, in my case its fruit so it would be oranges, lemons, limes etc
I will try your formula too, and also the module strud has provided between you both im sure you have solved my issue, and i appreciate your help
mehribon, open the file go to the developer tab, open vba (first icon) and open the module
or push ALT + F11
If that's sorted it please set the thread to solved
Thanks for the rep also
Not quite, i still cant get it to work..... I have tried both of your suggestions and still no joy,
If you dont mind taken another look i have attached the actual sheet i am trying to create this on, and it is in the summary tab where i am trying to place this formula
No worries if not, hopefully il get it eventually haha
Dan
Sorry - I can't find where you've put the formula I posted in this workbook?
Regards
Then changed the cell locations within the formula to pick up column a and b in the cost model tab, but would only return the first part and then blanks thereafter
Yes, and yes used CTRL + SHFT + ENTER
Sorry, i removed it, i had it in B4 on the summary tab
And what results did you get? I assume you adapted all the ranges/sheet names in the formula first, so that it matches your actual set-up? And that you followed my instructions re array formulas?
Regards
Can you re-post with your attempt in please so I can see what you did and where the error might lie?
Regards
No problems
Ah. You didn't mention having any blanks in the range...
=IFERROR(INDEX('Cost Model'!$C$6:$C$500,SMALL(IF('Cost Model'!$C$6:$C$500<>"",IF(FREQUENCY(IF('Cost Model'!$B$6:$B$500=$A$4,MATCH('Cost Model'!$C$6:$C$500,'Cost Model'!$C$6:$C$500,0)),MATCH('Cost Model'!$C$6:$C$500,'Cost Model'!$C$6:$C$500,0)),ROW('Cost Model'!$B$6:$B$500)-MIN(ROW('Cost Model'!$B$6:$B$500))+1)),ROWS($1:1))),"")
Regards
Thats perfect, i had to change ROWS($1:1))),"")
As when i was dragging the formula down into the lemon, lime sections it was saying ROWS($1:57))),""), ROWS($1:72))),"")
So i changed it to start again from ROWS($1:1))),"") and it works perfectly
THANK YOU
Ah sorry, i didnt think that would matter, i shall have a look, i appreciate your time and help
You're welcome.
I might just add that those merged cells will be a bit of a problem if you want to be able to drag that formula down and have it reference the relevant colour in each of those merged cells. Much better to have the colours repeated in all the relevant cells for that grouping, so that the formula, when dragged down, is correctly referencing the relevant colour.
If you don't want to 'see' the colours in every cell but say the central one, you could consider formatting with 'merge across selection', or even changing the font colour of all but one in each group to white.
Merged cells are a big issue in Excel, and best avoided. If you absolutely insist on keeping them, let me know as it will require some added intricacy in the solution I provided.
Regards
What i done was adjusted the formula to lock the merge cell into it, as you had done with $a$4 i just adjusted it each time it started at a new category and it seems fine
I understand what you have said, and if i have problems i think i will use the single cells and blank all but one out, for now its fine though thank you
Sure. Glad I could help.
All the best.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
shiftyspina 
Register To Reply
Bookmarks