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
  • 27,773
CH1234
  • 1
  • Can you provide an example of what isn't working? – Isolated Sep 19 '22 at 14:25
  • and please provide an example of what your end result should look like. – gns100 Sep 19 '22 at 15:34
  • Please demonstrate this "cross-referencing" problem you have, and why you believe it can be solved by "formatting"? Formatting in Excel does not change the value that is stored in a cell, only the way it is displayed, so it is difficult to understand what you are trying to do. – Ron Rosenfeld Sep 20 '22 at 01:31
  • I feel like we’re missing some piece of information here. Comparing 12345 and 12345.0 mathematically will result in a match. That they are not matching leads me to believe that Excel is defaulting to a text-wise match that will fail. I would consider forcing them both to text using the TEXT function with identical format strings, or forcing them both the numbers using the VALUE function. We need to actually see your formulas and/or and excerpt of your data. Often times, you simply have a syntax problem in a formula, as opposed to taking the wrong approach, and we won’t know. – Max R Sep 20 '22 at 04:34
  • I've just updated my post with further explanation and some examples. I hope this clears things up a bit.

    Also, while I didn't include the formula bar, the cell values are integers for both X and Y except for Y.n rooms, rather than Y values being Y.0 even in the case of only 1 room.

     – CH1234 Sep 20 '22 at 12:35

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,288
  • The ship has already sailed. That is, the formula has already completed it’s miscalculation before the conditional formatting is applied. – Max R Sep 20 '22 at 04:37
  • Thanks Adam. This did the job I needed (at least visually). I now need to see if my original plan for the data cleanup is going to work. – CH1234 Sep 20 '22 at 13:18