SUMIF - trim and concatenate 2 sets of 2 columns giving answer in another column

[chuf]

I’ve searched the forum but can’t find the answer to this question… I’m sure its easy given some of the amazing stuff I’ve seen you lot do.

I have two sets of two columns which contain numbers as text with trailing spaces and I want to concatenate column A and B and concatenate column F and G, and then compare them, and sumif value in column I if they are the same - giving the answer in the same row as the matching data in column A and B.. but in column J.

I hope i've explained myself..

I’ve seen sum product and haven’t been able to get it to work at all. I have been adding the trim() function in around the column references to see if that would work but am failing…

E.g.

A = 2000 text
B = 1234 text

F = 2000 trailing spaces, text
G = 1234 trailing spaces, text

I = 10,000

Answer in column J

I want 20001234 = 20001234 = 10,000 for the whole column.

I don't want to change the original data as its externally sourced and used for other links etc.. This would save a few hours a week if i ( more correctly, you ) could get this to work.

Many thanks. chuf

 

[DonkeyOte]

ok, I think we've been on crossed lines -- I was under the impression that the source data was stored as text and the criteria (unique entries) were stored as numbers... appears the opposite may be true...

change

=($B6+$C6)

to

=(0+$B6+$C6)

report back results

EDIT: just read your last post... yes the fact they are not all numbers will cause issues... can you provide a few examples of unique entries (B:C) ... thanks.

 

[chuf]

Hello.

the source data is stored as text with trailing spaces.

the criteria is also stored as text, but i can control those so they can be changed if necessary.. but i thought that if they were the same format this exercise may be "easier"

Added the 0 in and still not working.

B will be 1234

C could be either 5678 or AB01

This will be matched in the source data. with G potentially containing AB01 too.

Thinking on another exercise i need to do in a few weeks, being able to get this to work for text in both columns would be fantastic and save me another few hours a week - be out of a job at this rate

I really appreciate your help... sorry i'm not being more clear..

 

[DonkeyOte]

does this work for you ?

=SUMPRODUCT(--(TRIM('[Communities summary data.xls]summary'!$A$1:$A$65000)=TRIM($B1)),--(TRIM('[Communities summary data.xls]summary'!$G$1:$G$65000)=TRIM($C1)),--('[Communities summary data.xls]summary'!$K$1:$K$65000))

NOTE:
Only try on one or two rows of data... the size of your data range is huge (65000 rows) so this will be very memory intensive when it runs !

(Do you really have 65000 rows of data or did you add that range as a catch all for future usage ... try and keep as small as possible)

 

[chuf]

Hi.

No I'm afraid it still doesn't work.

I used 65000 as a catch all, i know there's a far better way of doing it with last row and - 1 or something ( i read a bit of it yesterday on here ) but i wanted to get the basics working first before trying to adapt further.

I will keep trying on this, i'm afraid i need to go out for a bit now.. food to be bought..
i will log back on later today .. i very much appreciate you helping me on a Sunday morning. Have a good day.

Chuf

 

[chuf]

Hi all,

i'm very grateful for the help this morning but this problem is still getting the better of me.

Does anyone have any other suggestions, thanks in advance.

 

[DonkeyOte]

I can't think of any reason why the formula provided does not work unless the ranges provided are incorrect.

Please stipulate clearly

a) where the criteria ranges can be found in terms of rows, columns, file names / sheets
b) where the source data can be found in same terms as a)

-- please ensure you detail entire ranges -- if you do not have data in 65k rows do not use this as your range - give yourself some flexibility above and beyond the last row of data if needed but make range as small as possible.

In reality it is difficult for those of us here to provide solutions when we are given one scenario which proves to be nothing other than hypothetical. It is much easier for all if the full & real details are provided at the beginning.

 

[chuf]

i appreciate the frustration. I will try and explain more clearly.


Spreadsheet: Communities summary data. Worksheet: summary has numbers or letters formatted as text with trailing spaces in column A and G. Values are shown in column K.

eg A - 1234 G - AB01 K - 1000.00

There may be more than one occurence of the above combination. Data is currently in lines 1 - 3600.

I have a spreadsheet: Department summary which may / will have the above 1234 and AB01 in columns B and C - also currently formatted as text but can be changed.

I wish to enter a formula in the Department summary spreadsheetl, column G which sumif's the criteria in B&C = those in A&G in the Communities summary / summary worksheet above.

 

[barry houdini]

Assuming your B1 and C1 entries in the Department worksheet don't contain any trailing spaces try this formula in G1

=SUMPRODUCT(--(LEFT('[Communities summary data.xls]summary'!A$1:A$3600,LEN(B1))=B1&""),--(LEFT('[Communities summary data.xls]summary'!G$1:G$3600,LEN(C1))=C1&""),'[Communities summary data.xls]summary'!K$1:K$3600)

 

[DonkeyOte]

which row is the first to house criteria in b/c -- ie which row will contain the first sumif formula result in column g ?

 

[chuf]

Mr BH Sir - it works, oh my god... YIPPEE

That is just fantastic.. i have been trying to think of how i could have changed what i needed to do to get around this, you're a star.

Very grateful to lasw10 for all his help, and apologies if i was unclear in my explanation.

You've made my day. Best wishes. Chuf