Count distinct orders with condition

[flaviooooo]

Hey,

I have an excel like this:

Week Order1 Order2 Order3
1 123456 123457 123457 01
2 123458 123459
2 123460


Now I would like to count the amount of distinct orders in each row. Watch out, an distinct order is only the first 6 digits. So in Week 1 for example 123457 and 123457 01 are actually the same order!

The result I would like is:
Week Order1 Order2 Order3 COUNT
1 123456 123457 123457 01 2
2 123458 123459 2
2 123460 1


Can this be done?

Thanks in advance

 

[Rofeu]

Will the orders be continious? i.e. will there never be

Order1 Order2
123456 123460

If so, you can use the formula below:

=LEFT(MAX(B1:E1),6)-LEFT(B1,6)+1

If not, it's back to the drawing board, I guess

Cheers,

Roel

 

[flaviooooo]

Hey Roel,

thanks for the response, but unfortunately it can occur that some orders are 'left out'...

Back to the drawing board indeed ;-)

 

[Rofeu]

assume the following series:

123456,123457,123460,123462,123470,123471,123471 01

Would your expected the count be 6?

If so, I believe the following is what you're looking for:

=SUM(--(VALUE(LEFT(B1:G1,6))-VALUE(LEFT(A1:F1,6))>0))+1

This will need to be entered as an array formula (ctrl-shft-enter)


Cheers,

Roel

 

[flaviooooo]

Hey Roel,

thanks again

Unfortunately I get an error #VALUE!, which I think is due to empty columns?

 

[Rofeu]

Hi,

indeed, empty cells in the referenced range will result in #VALUE.

try this instead:

=SUM(--(VALUE(LEFT(IF(ISBLANK(RC[-7]:RC[-2]),0,RC[-7]:RC[-2]),6))-VALUE(LEFT(IF(ISBLANK(RC[-8]:RC[-3]),0,RC[-8]:RC[-3]),6))>0))+1

(remember to enter it as array-formula - ctrl+shft+enter)

Cheers,

Roel

 

[Rofeu]

sorry, still had my R1C1 on.

=SUM(--(VALUE(LEFT(IF(ISBLANK(B1:G1),0,B1:G1),6))-VALUE(LEFT(IF(ISBLANK(A1:F1),0,A1:F1),6))>0))+1

Cheers,

Roel

 

[yogia]

Hi flavi00000:

I understand that your order entries are always going to be either 6 digits or 6 digits a space and then some additional characters. If do, then ...

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com