0

I'm having some difficulty formatting some data correctly and after looking around extensively in similar threads such as here, I've come looking for help.

I have two similar datasets (X and Y) and want to cross-reference a field. X is an external data source, in the form of a 10-digit booking number which is always an integer. Y interprets this number differently; if there is only 1 room in the booking, it will be an integer but if there is more than 1 room, the internal number will add a decimal for the second room onwards (i.e. room 1 = 12345, room 2 = 12345.1 ... ).

As such, to facilitate the cross-referencing, I need to leave the integers intact (without .0 at the end) while displaying the decimalised reference numbers accurately to 1 decimal place.

So far, I've tried MOD, TRUNC and ROUND to no avail, as well as various conditional formatting methods. Can't figure out what I'm missing or doing wrong. Excel version is 2202 as part of Microsoft 365 Apps.

Thanks!

Examples below:

Currently, I'm cross-referencing X value with Y value. X value is a total, while I need to SUM Y values if Y.n = X to ensure that X value = Y value

current

To shed some more light on why I need to do this - in some instances, there are duplicate values that should be ignored but in order to differentiate from these duplicate values and the additional rooms (Y.n), I need to tidy up the data. There are also some instances where multiple values will be associated with a Y reference that aren't duplicates.

Sorry for not providing examples sooner and for explaining in somewhat vague terms.

AdamV's solution did actually work visually, and now I need to test whether it will work for the various failsafes I need to implement.

AdamV's solution

phuclv
  • 30,396
  • 15
  • 136
  • 260
CH1234
  • 1

1 Answers1

1

Conditional formatting would be one way. A formula such as

=A1=INT(A1)

Will be true only for whole numbers. Format these using a number format such as

#0_._0;;0_._0;@

The _ underscore means "use the same space as the following character". The result will be that the numbers line up with the decimal ones whether left r right-aligned, because this pads with the space for a decimal point and a following digit.

AdamV
  • 6,396