• 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.

Value in cell dependent on unique value in other cell [SOLVED]

H

Hugo huijer

Member
Maybe this topic title is not really clear, but here goes:

I have a list of "order numbers". The list contains all the order numbers for a project. To accommodate further calculations, I want to have an extra (dummy) column next to the order numbers, that show a value based on the uniqueness of the order number.

If an order number is not unique (it's already featured somewhere else in the list), I would like the value of the dummy column to be 0. If it's the first time the order number is featured in the list, I would like the value to be 1.

For example, this makes it possible for me to apply conditional formatting, to show and hide certain parts of order data that would else be repeated multiple times. Please note that when an ordernumber is featured multiple times, the most abovest number of the lot should have the value 1, the rest 0. So that every ordernumber will be shown once in the list.

I hope you can help me! Thanks!
 
Hi Hugo ,

In your helper column , you can use a formula such as :

=IF(COUNTIF($A$2:A2,A2)<=1,1,0)

This assumes your data is in column A , starting from A2.

What this does is , for the first occurrence of the order number , whether unique or not , the helper column will contain 1 ; if the order number is unique , then this will be the only entry against this order number in the helper column.

If the order number is not unique , then subsequent entries of the same order number will have 0 in the helper column.

Narayan
 
Alright, I'm stuck again.... Jeez,,

So I now have this dummy column which shows either a 1 or 0, based on the number of times the corresponding value is featured in a list.

The reason why I want this column is because I want to base my conditional formatting on these values. for example:
row A contains the dummy values (0 or 1). Row C to Z contains order data. I want to hide the order data for that column, when the dummy value is 0. Is that easily possible??

Greetings
 
Hi Hugo,
Can you please elaborate the term.. HIDE,
* is this make the data invisible.
* or make the row Invisible.

If you want data as invisible, you can achieve by Conditional Formatting,
but for the second case, you have to VBA / Macro feature..
Please confirm..
 
Hi Hugo ,

Hiding a row is possible by using the AutoFilter feature ; actions such as hiding , unhiding cannot be achieved by Conditional Formatting , since hiding or unhiding a row / cell is not part of the Format feature.

Narayan
 
Sorry for my weak explanation. When the values are 0, I want the conditional formatting to make the color of the text white (same as background), hiding it like that. I still want the row to show though, so there are empty rows.

I know this can be done with conditional formatting, but I just wonder what the rule would look like. Ofcourse I want it to be something like 'A3=0 --> formatting with white text font. The point is basically, that I do not know of a way to quickly do this for all the rows, as I would have to manually change the rules for row 4,5,6 etc. I reckon there must be a faster way, that I don't know of yet.

Thanks!
 
Hi Hugo,

Select A2, and go to
> Conditional Formatting ..
> New Rules
> Use formula to determine which cell to format.
> Use Formula as
=COUNTIF($A$2:$A2,$A2)>1

Now Click on Format > Number > Category > Custom > in Type section use Format as ;;; ( yes.. only three semicolon)
Click OK OK.

Now Copy Cell A2
Select the complete area. and
paste > Special > All Merging Conditional Formatting, and press Enter..
 
Hi Hugo ,

See this file.

Change any order number which is unique to something which has already occurred earlier in the column , and see what happens. The CF has been applied in columns A through Z.

Narayan
 

Attachments

Allright guys, my challenge is appaerantly way harder then I first thought it would be.

Both your methods seem to work to make the repeated orders hidden. The count if formulas do their job.

In my spreadsheet, the table that is used to store all the orders is dynamic. The data is supplied by entering a userform. The userform will insert a new row to the table, which will then store the order data. When the userform adds a new row, it unfortunately messes up my series of formulas and conditional formatting. The rows will shift in the formula, so they will not refer to the correct cell anymore.

Is it still possible to do it now, or is this beyond Excel?

Thanks!
 
Hi Hugo ,

There is no reason why there should be any problem ; the CF rules can be applied to any fixed range ; if you think that your table will never exceed 1000 rows , apply the CF rules to the range C2 through Z1000.

As long as your macro is inserting the row at the bottom of the table , for each new row that is added to the table , the CF rules' Applies To range will increase by one row.

For example , suppose your initial table lies between C2 and Z17 ; the CF rules have been applied to C2 through Z1000 ; when you add row 18 to the table , the CF rules will be modified to apply to C2:Z1001 ; with another new row added , this will change again to C2:Z1002 , and so on.

All you need to do is ensure the macro does not insert rows inside the table ; every new row should only be added at the bottom of the table.

Narayan
 
Allright, thanks for your answer. I think my macro should be adjusted then. The current code for adding rows to my table is:
Code: 
With Worksheets("REGISTER DOCKET TRUCK EXPORT").Range("B13")
        .ListObject.ListRows.Add (1)
        .Offset(1, 0).Value = Me.txtDocketTruck.Value
        .Offset(1, 1).Value = DateValue(Me.cmbDate.Value)
        .Offset(1, 2).Value = Me.cmbFleetDesc.Value
        .Offset(1, 3).Value = Me.cmbFleetNo.Value
        .Offset(1, 5).Value = Me.cmbTruckDriver.Value
        .Offset(1, 6).Value = CDate(Me.txtStartTime.Value)
        .Offset(1, 7).Value = CDate(Me.txtFinishTime.Value)
        .Offset(1, 8).Value = CDate(Me.txtLunchTime.Value)
        .Offset(1, 10).Value = Val(Me.txtStartOdo.Value)
        .Offset(1, 11).Value = Val(Me.txtFinishOdo.Value)
        .Offset(1, 13).Value = Me.cmbMaterialRemoved.Value
        .Offset(1, 14).Value = Val(Me.txtDocketTip.Value)
        .Offset(1, 15).Value = Me.cmbLandfill.Value
        .Offset(1, 17).Value = Val(Me.txtLoadsRemoved.Value)
        .Offset(1, 18).Value = Val(Me.txtTonnageRemoved.Value)
        .Offset(1, 20).Value = Me.cmbPartBudgetRemoved.Value

This should change so that it would just insert a row at the bottom of the table? Does it matter if I have an automatic totals row at the bottom?

Thanks!
 
Hi Hugo ,

I am not sure about this , but the (1) inserts the new row at the top of the table ; if you use :

.ListObject.ListRows.Add

the new row will be added at the bottom of the table.

Narayan
 
Yes, that is correct. But when I delete that (1), then the row will be inserted at the bottom, but the data will still be inputted in the first row of the table. With the (1), the new row will be created at the top of the table, and will be filled with the data. Without the (1), the blank row will be inputted at the bottom of the table, but the data will just overwrite the row at the top of the table. How can I change my inputting codes to send the data to the row at the bottom of the table, instead of the row at the top?

Thanks!
 
Hi Hugo,

Assuming we are talking about "Input project contract budget" from you previous upload.
If its a table, no need to adjust conditional formatting, it will grow, automatically, just apply CF from the top of the table.
Check the attached, with CF in "REGISTER PROJECT" sheet, "ContractBudgetRegistry" table.
 

Attachments

Thanks for the help, but I just can't seem to get it to work.

I am in almost the same situation as my example, yes. It's just a userform that puts data in a inserted row of a table (just like the example). I use a CF formula just like the one in your example, but it somehow still messes up when I use my userform. I've tried everything that I can think of, but when a new row is added in the table, either the CF formula will refer to the wrong cells, or the same happens while also adding a brand new CF (which refers to even more wrong cells by the way). I know I shouldn't be struggling with this, but I am asking for your help again. Sorry people.
 
Hi Hugo ,

Would it not simplify matters if you uploaded your file ? You can remove the data but leave the macros and table layout intact.

Narayan
 
No need! I think I figured it out. Sorry for the inconvinience!!!!

I think Excel (for some reason) adjusted the CF rule when I selected the range it would apply to. So I first entered the CF rule, then the range it would apply to, and without knowing, I think Excel changed my rule according to my range (which made my CF act weird). I got it working now, so thanks heaps for your help!!!
 
You must log in or register to reply here.
Back
Top