• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Creating a unique column from a column of duplicates

W

Wulluby

Member
Hi,


I have a table with one column containing a lot of duplicates:

'Data'!$A$8:$A$1000


I want to create a unique column in another sheet which I have done with:

=INDEX('Data'!$A$8:$A$1000,MATCH(0,COUNTIF($AI$14:AI14,'Data'!$A$8:$A$1000),0))


The issue I am having is that it has also pulled across Subtotal Titles as well as rows where a user has entered data to represent Titles. I can identify the data I want to pull across because in the column next to it, ('Data'!$B$8:$B$1000), is a column containing a mix of text and data, (AAA001 for example).


Any pointers on how I incorporate a, 'only pull if there is contents in 'Data'!$B$8:$B$1000' into the function,

=INDEX('Data'!$A$8:$A$1000,MATCH(0,COUNTIF($AI$14:AI14,'Data'!$A$8:$A$1000),0))


Thanks in advance.
 
=if(left(B8,3)="AAA",0,INDEX('Data'!$A$8:$A$1000,MATCH(0,COUNTIF($AI$14:AI14,'Data'!$A$8:$A$1000),0)))
 
Thanks for the input Indian, I see the logic in what you're doing there and it's certainly a lot simpler than any way I've been trying it. I am still getting results from rows where the cell in column B has no entry though.


In order for the formula to return anything I am having to press Ctrl+Sht+Enter which is wrapping everything in curly brackets. Could this be causing the problem?
 
=IF(OR(LEFT(B8,3)="AAA",B8=""),0,INDEX(Data!$A$8:$A$1000,MATCH(0,COUNTIF($AI$14:AI14,Data!$A$8:$A$1000),0)))


Should work hopefully............
 
That has a different affect anyway :)


=IF(OR(LEFT(B8,3)="ART",B8=""),0,INDEX(Data!$A$8:$A$1000,MATCH(0,COUNTIF($AI$14:AI14,Data!$A$8:$A$1000),0)))


This time I have a 0 in the first row, 3 unique results only one of which has an ID in the B column, no more of the 12 or so unique results and a long run of 0s.


My brain is pickled with this one, will have a poke about at it in a bit and see if I can get it working.
 
Can you post a spreadsheet, I am in office hence dont have access to most shared sites, but I am sure someone would be able to assist.


http://chandoo.org/forums/topic/phd-forum-posting-rules-etiquette-pls-read-before-posting
 
Hi ,


Is it possible you can upload your worksheet ?


What kind of data is in your column A , numeric or alphanumeric ?


Narayan
 
Sure, I'm in the office too so will look at uploading tonight.


Column A is project Project Steps and holds info like:


Grand Average

ARCT Average

ARCT

ARCT

ARCT

Config Env Average

Config Env

Config Env

Config Env

Config Env

Imp db Average

Imp db

Imp db

Imp db

Imp db

Imp db


Column B holds The Activity Key for the Activity in Column C and holds data like, (na = empty cell):

na

na

na

ART001

ART002

na

na

ART003

ART004

ART005

na

na

ART006

ART007

na

ART008


Will have a look at uploading tonight.


What's interesting/frustrating is that if I copy and paste an extract of above into a new sheet and put it in column A and B respective starting at row 2 with row 1 holding a simple header then enter the following in column D it does what I want, I just can't seem to get that pullng the data from the DATA sheet into my dashboard input sheet when I modify to look at Data sheet or when I put it into the data sheet itself and modify the cell range.


=INDEX(A2:A17,SMALL(IF(ISERROR(IF(MATCH(IF(ISTEXT(B2:B17),A2:A17,""),IF(ISTEXT(B2:B17),A2:A17,0),0)=ROW(A2:A17)-1,ROW(A2:A17)-1,"")),"",IF(MATCH(IF(ISTEXT(B2:B17),A2:A17,""),IF(ISTEXT(B2:B17),A2:A17,0),0)=ROW(A2:A17)-1,ROW(A2:A17)-1,"")),ROW()-1))
 
if i understand correctly, you have two problems:

1) getting rid of the entries in col B wchihc are "na"

2) getting rid of duplicates from the remaining data set


the quick and dirty way is

1) to do autofilter. go to col b, click the dropdown for custom and choose not equal na.copy the resulting list to another location.

for 2) with this new list, create a pivot table where both col and col b are in the row section. then specify count of col a in the data area or something (doesn't matter). this should quickly produce a unique list of combinations for the two columns.
 
Have uploaded to


https://skydrive.live.com/redir.aspx?cid=b81cfd1b7bf7bb2c&resid=B81CFD1B7BF7BB2C!195&parid=root


1st sheet is a perfect working example of what I want to achieve but I can't seem to get that working outside that simple sheet. Data holds the user inputed data and the 3rd sheet is where I want to pull data to in order to feed the dashboard.
 
Hi Wulluby,


If you can allow a helper column then this one should work fine. Here is the modified file. Kindly give feedback.


http://www.4shared.com/file/AgSx12pD/Dummy_Worksheet-Reworked.html


Regards,

FASEEH
 
@Faseeh

I don't think there is anything wrong with a helper column but it would need to operate from the inputs sheet where the user is not seeing it. The data column is where a user would input data and I am trying to keep all things not related to the inputers interested out of sight. What I have uploaded is a very simplified overview of the relevant columns, there is a lot of other stuff up there like staff resourcing, planning dates, actual dates, durations etc.


I removed the OR to =IF(ISBLANK(B8)=TRUE,"N",ROW(A8)) and that works as well for the helper column ;)


Not sure what you've done on the working example as it isn't working any more, I'm on 2003 at work so I guess it's something to do with conversion from the xlsx file I took down from 4shared, I'll have a look at it at home where I have 2010. The data that I am keen to grab is the name of each project step from the Data sheet and put that in the table on Inputs sheet, starting at AI16. So something like


ARCT

Config Env

Imp db

Imp GUI

Approver Pg


Idea being that I can then use an Offset formula to create a pie chart on a dashboard sheet which will allow selection of a project step and display it's %age.


It's not quite working but I it's food for thought thanks.
 
Hi Wulluby,


I have tried once again according to what i can understand from your post. I am attaching file, have a look at "ReWorked". Was this that you needed? :p


http://www.4shared.com/file/XUFaD6jx/Dummy_Worksheet-Reworked_2.html


Just need to enter the project step in red colored cell. It is excel 2007 version so I Hope it works with Excel 2010!! :)


Faseeh
 
It's almost there although due to lack of experience in Excel I'm not quite fully understanding how you've got that like that. I'll need to investigate a little.


Looking at 'Project Step' info in 'Reworked' column C. I noticed that when I try to add a new one at the bottom of the 'Data'A which is something that will happen, it didn't auto update in 'ReWorked' C. I tweaked the data in C from Data!$A$8:$A$34 to Data!$A$8:$A$1000 and could then add in 'Data'A and see it update, (I noticed I had to tweak each row individually in order to avoid an error and the select all ctrl+shft+ent wouldn't allow me to manipulate the range).


I thought brilliant exactly what I want done in 1 column, but then I noticed that my entry had no Activity ID in B meaning that it could have nothing to do with desired measurable data but just a user inputting something that was more for his visual aid. So I'm guessing that the rest of the columns such as Helper, (‘Data’G21 and ‘Reworked’E4?), are required to have that work that way so that ‘Reworked’C is not picking up any titles or superfluous data.


Will have to investigate and try and get my head round the cogs that you have working in there :) If I can get it to dynamically update the Project steps in the table in the 'input' sheet then I can look at working the average percentages complete on the project steps.
 
I've just pasted what you have in 'Reworked' column C into my project and it's pulling back titles still but I'm thinking, am I making this more complicated than it needs to be?


Just pull up every 3rd row in the offset instead of 1 to allow for the subtotal group header and the manually entered title to display in a pie chart on the dashboard and keep the formulas in that table simple. Will have a mess around with it. That would require an inputting user to be doing this for each different project step and to not be breaking the Subtotal groupings.
 
hi Wulluby,


Nice to read your feedback. This has been for the first time that i am 'trying' to help someone else. Quite often i have been stuck with my own worksheets and ask for help myself... heheheh :)


You are right that it will not accommodate new entries in your Data Sheet. An alternate could be to use named ranges to overcome this problem, meanwhile i try to do something else to overcome this problem.


Regards,

Faseeh
 
Hi Wulluby,


This one is accommodating both new row and columns, just drag the formula down/across the sheet. ;) Hope it will be helpful.


File: http://www.2shared.com/file/7oEwIyDi/Dummy_Worksheet-Reworked_Final.html


(Caution: Don't write any thing in Data!A1:A7)


Regards,

FASEEH
 
Hi Faseeh,


I appreciate your input, I know how you feel. I only joined last week and feel I've got a lot of info off the site and forums and wish I could give something back in the way of help rather than just take.


In work at the moment so will take that new sheet down when I get home and have nosey
 
@SirJB7


Thank You Sir for your feedback. :) Learning is always a two-way, the more i share, the greater I learn. Kindly feel free to point out mistakes and any thing that can be done in smarter or improved way!!


Faseeh
 
Sorry Faseeh, late night at work last night and by the time I got home to a win7 build I couldn't face looking at another computer screen. Will try and look at it tonight but to be honest I am worried about the but you mention about not typuing above a row, which is where the title bar is :)


I have found another solution, there is a pull down combo box pulling the list of projects which means there's an array with them. So able to pull the info from that. I need to learn when I'm overcomplicating things when there is a much much simpler answer I think.


I also need to read a lot more of the simpler tutorials. I'll be looking at yours in more detail though because there's some good stuff int here worth learning. Thanks for your input :)
 
Hi Wulluby,


We are all busy so no need to explain. The caution was due because there is counter that counts blank cells in column A:A of "Data", adjust it with CountA(A1:A7) in the formula so that actual no. of cells "not blank" should always equal your entries, (currently you will find formula with a hard-set "-2" to less two other text entries in that column) you can get rid of this caution!!


BTW, will you share the smarter solution you have found? :p I will be glad if that is something new to me.
 
Hi Faseeh,


I wouldn't call it a smarter solution to be honest, more of a cheat. As there is a combo list requiring to pull the project step names from an array, I have set up a validation table with the proj steps. Next step is to read the article on how to create the dynamic validation table and I have a unique list which is updated everytime a new proj step is inputted. It won't see the proper heavy use of a tool getting real world use and it's more of a project for myself to learn some tricks and what not. Although on that note I do need to learn the ideas that you are throwing in so will be grateful of the worksheets you have put in to go over and learn.
 
You must log in or register to reply here.
Back
Top