Ignoring spaces

  • Thread starter Thread starter jsollman
  • Start date Start date
J

jsollman

I need to collect part status information from 2 sources. The problem i
that one of the sources is placing spaces before the part number. M
worksheet is using column "D" for my part numbers. Source 1(MES) i
entering a productsum in column "AB", Source 2(SCIM) is entering
productsum in column "AC".

This formula works great:

=SUMPRODUCT(--('S:\Shared Machining\625\[8358.xls]rptSingleSection.as
ilc=No&sec'!$D$2:$D$5000=D5),--('S:\Share
Machining\625\[8358.xls]rptSingleSection.as
ilc=No&sec'!$H$2:$H$5000="F"),'S:\Share
Machining\625\[8358.xls]rptSingleSection.asp ilc=No&sec'!$I$2:$I$5000)


This one will return 0 for the first array always unless I add th
spaces to my parts column "D"

=SUMPRODUCT(--('Daily Report.csv'!$B$2:$B$5000=D5),--('Dail
Report.csv'!$E$2:$E$5000="TRIGGER "),'Daily Report.csv'!$C$2:$C$5000)

I figure that the only way to "ignore" the spaces is going to be i
VBA, but I'd really appreaciate a "nudge" in the right direction.

Thanks
Jeffery Sollma
 
Hi
use TRIM to remove the spaces:
=SUMPRODUCT(--(TRIM('S:\Shared
Machining\625\[8358.xls]rptSingleSection.asp
ilc=No&sec'!$D$2:$D$5000)=TRIM(D5)),--('S:\Shared
Machining\625\[8358.xls]rptSingleSection.asp
ilc=No&sec'!$H$2:$H$5000="F"),'S:\Shared
Machining\625\[8358.xls]rptSingleSection.asp ilc=No&sec'!$I$2:$I$5000)

--
Regards
Frank Kabel
Frankfurt, Germany

jsollman > said:
I need to collect part status information from 2 sources. The problem is
that one of the sources is placing spaces before the part number. My
worksheet is using column "D" for my part numbers. Source 1(MES) is
entering a productsum in column "AB", Source 2(SCIM) is entering a
productsum in column "AC".

This formula works great:

=SUMPRODUCT(--('S:\Shared Machining\625\[8358.xls]rptSingleSection.asp
ilc=No&sec'!$D$2:$D$5000=D5),--('S:\Shared
Machining\625\[8358.xls]rptSingleSection.asp
ilc=No&sec'!$H$2:$H$5000="F"),'S:\Shared
Machining\625\[8358.xls]rptSingleSection.asp
Click to expand...
ilc=No&sec'!$I$2:$I$5000)


This one will return 0 for the first array always unless I add the
spaces to my parts column "D"

=SUMPRODUCT(--('Daily Report.csv'!$B$2:$B$5000=D5),--('Daily
Report.csv'!$E$2:$E$5000="TRIGGER "),'Daily Report.csv'!$C$2:$C$5000)

I figure that the only way to "ignore" the spaces is going to be in
VBA, but I'd really appreaciate a "nudge" in the right direction.

Thanks
Jeffery Sollman
Click to expand...
 
Back
Top