Allocating specific booking numbers to specific customers - Help needed

[deankavon]

Hello!

So, i have the following problem;

i have a powerpivot table with specific data as booking numbers and margins made on those bookings.

Each specific booking starts with a number.

for example:

110000/13 or 120000/13

What i need to do is to connect these booking numbers and associated margins to a specific customer;

110000/13 - Customer A
120000/13 - Customer B

And also i need to connect 5 different booking numbers to one specific customer.

These booking numbers start from 0000 and continue on.

I basically need them to be connected and to allocate each new booking number to the specific customer.

Now, the thing is i dont have the customers in this powerpivot table.


Help would really be much appreciated!

 

[Jacob Barnett]

Still not sure where you are going with this! If this is what you are trying to do:

Then this formula (in B2) does the trick:

=(VALUE(LEFT(A2,4))+COUNTIFS(A$2:A2,A2))&"-"&RIGHT(A2,2)

Again I'm not sure how this helps but hope it does.
Jacob

 

[deankavon]

Thank Jacob for all your effort!

This is what i am trying to accomplish:

[TABLE="width: 205"]

<tbody>[TR]
[TD]CUSTOMER[/TD]
[TD]BOOKING NUMBER[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]10000-12[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]10001-12[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]10002-12[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]10003-12[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]10004-12[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]10005-12[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]10006-12[/TD]
[/TR]
</tbody>
[/TABLE]


i've managed to use this as a solution but it takes me some 80 000 rows in the updated table to input all booking numbers :s

In my original pivot table booking number are with a slash not /...is there any formula in powerpivot that could associate like the example above certain booking nums to one specific customer..? i hope it is clearer now. :s

thnx!

 

[deankavon]

@Jacob

Maybe it would be simple if i explain it like this;

Booking numbers starting from 10000/12 to 10999/12 are for Customer A.

Booking numbers starting from 20000/12 to 20999/12 are for Customer B.


All these booking numbers are already in my powerpivot table that is connected do the main database with the reference "booking". but they are all mixed up between each other as they occur randomly.

Is there any formula that can create a calculated column, that if booking number is from 10000/12 to 10999/12 then the calculated column states "customer A"?