if this concatenated number is either 1 to 1 match or any number of even matches then give 0

[jordanburch]

Hi All

I have the following data. Trying to simply subtract two contactenates with vlookups its giving me a value error. Please help! so the formula is working in aa where it says 0.00 but further down you see the same formula showing as a value which is the same number. The formula in aa is x-y-z. In x there is a concatenate in y and z there is vlookup. Let me know if you need anything else.

Jordan

 

[etaf]

thanks
BUT the card number column - which i assume was the burner column Before
is ODD and not even and so would be false - as you have 7 entries
column J tests for that ????
and so you dont get the 0

Also for card 1 - the total does not mauch the total DB for card 1

i'm not sure i'm getting the question now
you orginally asked

Trying to simply subtract two contactenates with vlookups its giving me a value error.

so where is this now on the simple spreadsheet

or has the question changed completely now

please bear with me , i'm trying to understand your spreadsheet and process and i have no idea what it is until you explain it to me

 

[jordanburch]

thanks
BUT the card number column - which i assume was the burner column Before
is ODD and not even and so would be false - as you have 7 entries
column J tests for that ????
and so you dont get the 0

Also for card 1 - the total does not mauch the total DB for card 1

i'm not sure i'm getting the question now
you orginally asked

so where is this now on the simple spreadsheet

or has the question changed completely now

please bear with me , i'm trying to understand your spreadsheet and process and i have no idea what it is until you explain it to me

so you can ignore previous things. My work is to the very right where it is not either green or red. The card number is the same card number and those are transactions that are specific to that card number. I need it to match only if there are an even number of matches or a one to one. However we get there doesnt matter I may be over complicating things with my formulas but its the only way I can think of for now. Then the red ones have the same card number and even dollar amount but there are three on the cr column and only one in the db column so we would not delete those. Makes sense?

 

[etaf]

so you can ignore previous things.

OH OK,

My work is to the very right where it is not either green or red.

column Q to AG - is what we are looking at now then

I need it to match only if there are an even number of matches or a one to one.

what does that mean - match what and 1 to 1 what

Then the red ones have the same card number and even dollar amount but there are three on the cr column and only one in the db column so we would not delete those.

I cannot see RED in the Q to AG
Only C48 to D51
and to be honest i'm not following at all now
probably me ,

Whats the objective
To match the transactions on 1 card where the credits and debits result in a zero , providing the amout of transactions on the card are even .....
so you could have 10 credits all of £1 - 10 rows
and would need to have 2 debits (to make EVEN) which total £10 - so maybe a £2 and an £8

right at the beginning

I started out looking at concatenated values would not subtract ...... as quoted
NOW
i dont know what i'm doing
really sorry

 

[jordanburch]

OH OK,

column Q to AG - is what we are looking at now then

what does that mean - match what and 1 to 1 what

I cannot see RED in the Q to AG
Only C48 to D51
and to be honest i'm not following at all now
probably me ,

Whats the objective
To match the transactions on 1 card where the credits and debits result in a zero , providing the amout of transactions on the card are even .....
so you could have 10 credits all of £1 - 10 rows
and would need to have 2 debits (to make EVEN) which total £10 - so maybe a £2 and an £8

right at the beginning

I started out looking at concatenated values would not subtract ...... as quoted
NOW
i dont know what i'm doing
really sorry

So basically look in column CD if it is marked yellow i want it to match and delete. If it is marked red I do not want to delete any of those lines since there is not an equal amount on both the debit and credit side. Does that make sense?

 

[etaf]

in column C and D - nothing in column CD

rows 48 to 51 are filled RED in column C & D
its not marked yellow
its marked red and you dont want to delete
OK
as not an equal amount on Credit and Debit

so how did you decide to ONLY look at row 48 - 51

this is slow , sorry i'm not following

Maybe better to see if other members can help , i'm sure not helping you and likely only frustration - but this now post 15 , maybe unlikely to be looked at

 

[jordanburch]

in column C and D - nothing in column CD

rows 48 to 51 are filled RED in column C & D
its not marked yellow
its marked red and you dont want to delete
OK
as not an equal amount on Credit and Debit

so how did you decide to ONLY look at row 48 - 51

this is slow , sorry i'm not following

Maybe better to see if other members can help , i'm sure not helping you and likely only frustration - but this now post 15 , maybe unlikely to be looked at

Yes you catching on. I looked there because the card number was the same and the dollar amounts were the same meaning a potenatial match but its not since there are three on d and only 1 in c. But when you go to the yellow both the card number and dollar amounts match and its an even number of matches meaning there is one in c and one in d. if you did count if on a concatenate of b and c and then b and d and then a count if that would should you how many times that value is showing up and it should be equal. even if its three in column d and 3 in column c I want it to match, I just thought of this so this info is new. The time you would not match is if that concatenate is 1 in column c and 3 in column d. Other matches could be 5 in column c and 5 in column d as long as the card number and dollar amounts are the same.