Sumif: Larger string not found in smaller string

[a5ad]

Hi All,

I have 2 sets of data that I need to reconcile against each other. The sumif works on the smaller text looking into the larger text but I cant make this work the other way round.

The Data sets are as follows:

Firstly Card, this is the reference number the bank gives me every time we have a card transaction. A single card transaction can be used to account for many purchases.

Secondly the Sales transactions, all sales are recorded with additional information to that of the Card transactions.

Column	B	C	D	E	F	G	H	I	J
Row 2	Card	£		Sales Info	£		Card to Sales		Sales to Card
3	100000​	10​		Monday111111March	1000​		0​		0​
4	222222​	2000​		Tuesday222222April	2000​		2000​		0​
5	300000​	10​		Wednesday333333May	3000​		0​		0​
6	400000​	10​		Thursday444444June	4000​		0​		0​
7	500000​	10​		Friday555555July	5000​		0​		0​
8	333333​	3000​		Saturday666666August	6000​		3000​		0​
9	600000​	10​		Sunday777777September	7000​		0​		0​
10	700000​	10​		Monday888888October	8000​		0​		0​
11	800000​	10​		Tuesday999999November	9000​		0​		0​
12	444444​	4000​		Wednesday101010December	10000​		15000​		0​
13	900000​	10​		Thursday444444June	11000​		0​		0​
14	110000​	10​		Friday303030February	12000​		0​		0​
15	120000​	10​		Saturday404040March	13000​		0​		0​
16	130000​	10​		Sunday505050April	14000​		0​		0​
					Formula's		=SUMIF(E:E,"*"&B2&"*",F:F)		=SUMIF(B:B,"*"&E2&"*",C:C)

I can get a result when I search for the Card reference (column B) in Sales Info (column E) using =SUMIF(E:E,"*"&B2&"*",F:F) to get the result as shown in Card to Sales (column H), but I can't seam to get a result when doing the reversal by checking column E to B.

Any help would be greatly appreciated.

Regards

Asad

PS apologies if this question has been answered before, I couldn't find anything on this particular topic.

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Row 2	Card	£		Sales Info	£		Card to Sales		Sales to Card
2	3	100000	10		Monday111111March	1000		0		0
3	4	222222	2000		Tuesday222222April	2000		2000		2000
4	5	300000	10		Wednesday333333May	3000		0		6000
5	6	400000	10		Thursday444444June	4000		0		10000
6	7	500000	10		Friday555555July	5000		0		0
7	8	333333	3000		Saturday666666August	6000		3000		0
8	9	600000	10		Sunday777777September	7000		0		0
9	10	700000	10		Monday888888October	8000		0		0
10	11	800000	10		Tuesday999999November	9000		0		0
11	12	444444	4000		Wednesday101010December	10000		15000		0
12	13	900000	10		Thursday444444June	11000		0		10000
13	14	110000	10		Friday303030February	12000		0		0
14	15	120000	10		Saturday404040March	13000		0		0
15	16	130000	10		Sunday505050April	14000		0		0
Master

Cell Formulas
Range	Formula
H2:H15	H2	=SUMIF(E:E,"*"&B2&"*",F:F)
J2:J15	J2	=SUM(FILTER($F$2:$F$15,ISNUMBER(SEARCH($B$2:$B$15,E2)),0))

 

[a5ad]

Hi Fluff,
Thank-you for the warm welcome.

I can see what you have done but unfortunately it does not resolve my issue. The card numbers in column B need to be an exact match (or thereabouts) as there are over daily 500 transactions to match off. Its generally 6 digits but can also be 4/5 digits if the numbers at the start are a zero they can often be omitted from the report.

 

[Fluff]

Is the Sales info just one string, or do the card nos have a space either side?

 

[a5ad]

Is the Sales info just one string, or do the card nos have a space either side?

The sales info is like the above but the information can be anywhere in the string, unfortunately no fixed format.

The card information generally has some other text infront of it but is not as long as the Sales info.

 

[Fluff]

In that case both formulae will fail & I'm not sure there is anything you can do about it.