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]

cant see the formula anywhere to be able to tell
can you post the formula(s)
also extracts and concatenates may be returning the number as text , as maybe vlookup - so sometime needs to have a *1 to change to a real number

or better still
Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[jordanburch]

formula in x =+CONCATENATE(U3643,V3643)

formula in y =+VLOOKUP(U3643,V:V,1,FALSE)

Formula in z = =+VLOOKUP(V3643,U:U,1,FALSE)

formual in aa =+IF(AND(ISTEXT(X3643),ISTEXT(Y3643)),X3643-Y3643,X3643-Z3643)

my company wont allow me to install the minisheets thig so I posted the above formulas with the correspodning columns and it is the first row in the formulas.

 

[etaf]

ok, so i cannot see the values the vlookup is using without typing it all in
any way to add to a share - which was the alternative

as i mentioned cocatenating numbers will result in TEXT and so cannot be subtracted
i would use a *1 after the concatenate to change to a number

as you can see
left justified numbers ARE text, and if combined in vlookup etc - maybe your issue

so use *1 or +0 to change any cell you want to show as a number

Book1
	A	B	C	D
1			left justified - TEXT	right justified - Number
2	11	100	11100	11100
3	12	200	12200	12200
4	14	300	14300	14300
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=A2&B2
D2:D4	D2	=(A2&B2)*1

 

[jordanburch]

Give me 1 hr I'll try gotta doctoroa appoint thanks for your help so far

 

[jordanburch]

Loading Google Sheets

Ok here is the link trying to basically see if column b and c and d if they are the same number number and the same amount of instances in c and d basically mensing they are a match in credits and debits I want it to filter down for those only that have equal amounts of concatenate bumer and DB and then concatenate bumer and cr columns and if they are either a 1 to 1 match or if they have an even amount of transactions on each side like column c has 6 5407735.15 and columns d has 6 5407735.15 then we filter down to those matches and I will delete them. It cannot delete if there are an odd number of matches as that would not be appropriate does that make sense?

 

[etaf]

has this now changed

I have the following data. Trying to simply subtract two contactenates with vlookups its giving me a value error.

looks a lot more complicated , then just subtracting two concatenated numbers

row 107 and 108 - seem to match the ID and the amount from credit and debit and an even count - so column L is showing zero - so seems to do what you say in yuor paragraoh - but to be honest o was not following that well

each side like column c has 6 5407735.15 and columns d has 6 5407735.15

wheres 6 5407735.15 in column C - searched and did not find

so not sure from that large spreadsheet - WHAT is NOT working

we only need a small sample of data - showing where its WRONG and what the expected results SHOULD BE

 

[jordanburch]

has this now changed

looks a lot more complicated , then just subtracting two concatenated numbers

row 107 and 108 - seem to match the ID and the amount from credit and debit and an even count - so column L is showing zero - so seems to do what you say in yuor paragraoh - but to be honest o was not following that well


wheres 6 5407735.15 in column C - searched and did not find

so not sure from that large spreadsheet - WHAT is NOT working

we only need a small sample of data - showing where its WRONG and what the expected results SHOULD BE

so row 3463 is 25 in debit if you scroll down to see the other yellow credit both those numbers and credit and debit amounts match. In colmun ag3643 the value is 0.00 thats what I was expecting, but in ag3653 is the same value as in ae 3644 hence i was expecting a 0.00 in ag3653. This can only be if there is a one to one match or if there are an even amounts on the debit side as to the same even amounts on the credit side, meaning if I have 4 contactenates that are the exact same 4 concatenates on both sides then I want zero or whatever number to filter for to be able to delte those rows. hopefully that makes sense

 

[etaf]

row 3463 - i cannot see 25
nothing in cell AG3463 , in fact in any cell

you need to simplify the example - maybe cut a lot of the rows off and just have 10 or 20
working around 1000's of rows is not great

i have tried ..... but see if you can make it easy for me or other members to help

 

[jordanburch]

Loading Google Sheets

Here is condensed version. Basically the two yellows need to be matched and deleted and the red ones do not because there is not the same number on the DB column as the credit column. Make sense?