Hi,
I am facing a problem with the matching that is not producing the desired results.It might be due to the spaces in left hand side of each.The below formula I am using.How can I revised the above said formula so that perfect match can be done which is currently is due to spaces.
=SUMIFS(DO!$Y$2:$Y$431,DO!$A$2:$A$431,SO!B2,DO!$T$2:$T$431,SO!A2,DO!$V$2:$V$431,SO!V2,DO!$X$2:$X$431,SO!AJ2)
Thanking you in anticipation.
Try: =SUMIFS(DO!$Y$2:$Y$431,DO!$A$2:$A$431,"*"&SO!B2,DO!$T$2:$T$431,"*"&SO!A2,DO!$V$2:$V$431,"*"&SO!V2,DO!$X$2:$X$431,"*"&SO!AJ2)
The above formula is perhaps not working.Originally Posted by Phuocam
Your data is a mess!
You have leading space characters in some cells and some cells have as many as 173 space characters in them.
In the SO sheet column AJ has many leading spaces and in the DO sheet column X has both leading spaces and cells full of spaces.
I ran the macro at this link to remove them.
The macro at that link will remove all leading/trailing
and multiple interspersed char 32 space characters.
It will also remove and/or convert char 160 non breaking
spaces into standard char 32 space characters. It will
work on text or numbers and the numbers will be
converted to true numeric numbers.
I use this macro dozens of times every single day!
It's a real time saver.
In addition, most of your data cells are formatted as TEXT. This includes numeric values.
You should avoid applying type formats as much as possible.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
I Have another
Can you let me know,what is the difference in this(taking a lot of time in large data) and above Post 10 (as you said) and which one is better that can do perfect match ?Sub CLEAR() Dim cell As Range For Each cell In Selection cell.Value = Trim(cell.Value) Next End Sub
The purpose of using above formula is to get the report in a separate sheet of Y column of SO & Y column of DO by matching :
SO sheet - A,B,V,AJ and get in one column of Y output (Gross qty)
DO sheet -A,T,V,X and get in another column of Y output (Gross qty)
And getting the differences in third column.For this I am using Pivot table after using above formula
Also suggest me if there is an alternative and best method.
Thanx again for your kind support.
Last edited by paradise2sr; 03-13-2016 at 10:46 PM.
I'm not a programmer so I can't tell you if one is better than the other.
One is more extensive, that's for sure!
Where are the spaces?
What does your data look like?
upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Dear Sir,
The data is exported from a software where it has more than 3000 data.The problem I have been facing is that of spaces in extreme left.It is clear that above mentioned formula I am using and on using trim to each it is creating an error.
You don't need to post the whole file with 1000's of rows of data.
Just enough data to demonstrate the problem. 20 rows worth of data is plenty.
As requested previously post a small sample Excel file.
Hi,
Pls find in attachment.The formula is in BA in SO Sheet.AJ of SO Sheet and 'X' column of DO sheet could not able to remove space by find & replace.
Dear Sir,
Thanx for your reply.You mean to say
Sub TrimALL() 'David McRitchie 2000-07-03 mod 2000-08-16 2005-09-29 join.htm '-- http://www.mvps.org/dmcritchie/excel/join.htm#trimall ' - Optionally reenable improperly terminated Change Event macros Application.DisplayAlerts = True Application.EnableEvents = True 'should be part of Change Event macro If Application.Calculation = xlCalculationManual Then MsgBox "Calculation was OFF will be turned ON upon completion" End If Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim cell As Range 'Also Treat CHR 0160, as a space (CHR 032) Selection.Replace What:=Chr(160), Replacement:=Chr(32), _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False 'Trim in Excel removes extra internal spaces, VBA does not On Error Resume Next 'in case no text cells in selection For Each cell In Intersect(Selection, _ Selection.SpecialCells(xlConstants, xlTextValues)) cell.Value = Application.Trim(cell.Value) Next cell On Error GoTo 0 Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub
I kindly request the forum contributor to shift this post to group "Excel Programming / VBA / Macros" to get the vba expert opinion in this regard.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks