SOLVED

Applying editing to imported data

Copper Contributor

Hi all,

I'm importing data from a third party excel worksheet to my own database. The source unfortunately have already edited their data to display negative numbers in red having removed the - sign. 

tss808_0-1642447788743.png

Is it possible that I can continue to import the data and for any red numbers to automatically be identified as negative and numbers in black as positive? I don't need to display the negative sign but I would need to be able to identify in this situation that the total gross positive was 216.8 (sumif(range,">0")) and the net was -97.2 (sum(range)) and also to be able to sort positive to negative. 

Any help would be appreciated. 

 

 

4 Replies
if this is coming from excel those numbers are most likely just formatted that way and the actual VALUE is still a negative number. Hence if you just change the number format you can have it shown as black with "-" sign or inside brackets [ neg number ] or any way you wish. Look at Home tab, number formats

Unfortunately not. I should qualify and say that this data has been pasted onto an email from a source file. I can copy and paste the cells and it retains the color but no other way to identify positive or negative other than colour.

best response confirmed by tss808 (Copper Contributor)
Solution

@tss808 

 

Hello, attached is an example using VBA. It works for the color red.

It is going to do an "if the color is red" then change the sign to negative else "leave the value as it is". 

 

 

alannavarro_0-1642451027675.png

 

nice but I wonder if his color is "Red" or just some shade of red. I haven't opened your code so maybe it already does this, but what might be nice is a way to find any color similar to red. You can get the RGB value and I don't know but maybe any case where R is > G and > B (I'm really no color expert so have no idea what would define shades of red).
1 best response

Accepted Solutions
best response confirmed by tss808 (Copper Contributor)
Solution

@tss808 

 

Hello, attached is an example using VBA. It works for the color red.

It is going to do an "if the color is red" then change the sign to negative else "leave the value as it is". 

 

 

alannavarro_0-1642451027675.png

 

View solution in original post