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.
Please Login or Register to view this content.
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)
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.
As requested previously post a small sample Excel file.
You don't need to post the whole file with 1000's of rows of data.Originally Posted by paradise2sr
Just enough data to demonstrate the problem. 20 rows worth of data is plenty.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
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.
The above formula is perhaps not working.
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.
Dear Sir,
Thanx for your reply.You mean to say
Please Login or Register to view this content.
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 ?Please Login or Register to view this content.
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!
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