Formula required to completed the below condition

A

Achyuth

New Member
Joined
Aug 13, 2021
Messages
8
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hello Everyone,

I have a query below with these condition, requesting to please help me solve this.

QA TagMod TagDifference
0c103e02,9961fd48,a4b63c62,a7eb2d76,d044fd17,ea1bea7e725f5ee4,a7eb2d760c103e02
0c103e02,9961fd48,a4b63c62,a7eb2d76,cafa8afb,d044fd17,ea1bea7e9961fd48,a4b63c620c103e02
18915865none18915865
344922fa71a81f31344922fa
4f9ff4d8none4f9ff4d8
6782f693none6782f693
6782f693none6782f693
6782f693none6782f693
6782f693none6782f693
725f5ee4,df494a74,f7205075df494a74,f7205075725f5ee4
809d1041a7eb2d76809d1041
d8d0d525noned8d0d525

I have used this formual which returns only one value which is not there, is there a way to return all the values present, I mean up to 10 missing values.

Please help

Formula I have used is

=SUBSTITUTE(TRIM(SUBSTITUTE(CONCAT(IF(MID(","&S111&",",ROW(INDIRECT("1:"&LEN(S111)+1)),1)=",",IF(ISERROR(SEARCH(MID(","&S111&",",ROW(INDIRECT("1:"&LEN(S111)+1)),FIND(",",","&S111&",",ROW(INDIRECT("2:"&LEN(S111)+2)))-ROW(INDIRECT("1:"&LEN(S111)+1))+1),","&T111&",")),MID(","&S111&",",ROW(INDIRECT("1:"&LEN(S111)+1)),FIND(",",","&S111&",",ROW(INDIRECT("2:"&LEN(S111)+2)))-ROW(INDIRECT("1:"&LEN(S111)+1))+1),""),"")),","," "))," ",",")
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
Welcome to the MrExcel board!

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Could you explain more fully what columns you have shown, what column the formula is in and just what the formula is supposed to do?
It would also help if you included the result(s) that you do expect.
 
Upvote 0
Peter_SSs said:
Welcome to the MrExcel board!

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Could you explain more fully what columns you have shown, what column the formula is in and just what the formula is supposed to do?
It would also help if you included the result(s) that you do expect.
Click to expand...
I have used the formula already in differences column, which is 3rd one.
Below is the condition which I am trying to achieve
1)I am checking if 2nd column has similar value as 1st column, in that case I just need to ignore
2) I am checking what the values are missing from 2nd column and returning only values which are missing in 2nd column and return the missing value from 1st column only in 3rd column.

above is the Formula which I have used in 3rd column
 
Upvote 0
Your profile says that you have Excel 365. Have you tried your existing formula with that version? For me it produces different results to what you have shown in post #1. In fact I think it might be the results you want? Column U below is your formula in my 365.
If they are the results you want then I think we can get a simpler (& non-volatile) 365 formula to do the job - see column V

Does any suggestion have to work in all three versions you have listed, or would a 365 solution be enough?

Achyuth.xlsm
STUV
110QA TagMod TagDifference
1110c103e02,9961fd48,a4b63c62,a7eb2d76,d044fd17,ea1bea7e725f5ee4,a7eb2d760c103e02,9961fd48,a4b63c62,d044fd17,ea1bea7e0c103e02,9961fd48,a4b63c62,d044fd17,ea1bea7e
1120c103e02,9961fd48,a4b63c62,a7eb2d76,cafa8afb,d044fd17,ea1bea7e9961fd48,a4b63c620c103e02,a7eb2d76,cafa8afb,d044fd17,ea1bea7e0c103e02,a7eb2d76,cafa8afb,d044fd17,ea1bea7e
11318915865none1891586518915865
114344922fa71a81f31344922fa344922fa
1154f9ff4d8none4f9ff4d84f9ff4d8
1166782f693none6782f6936782f693
1176782f693none6782f6936782f693
1186782f693none6782f6936782f693
1196782f693none6782f6936782f693
120725f5ee4,df494a74,f7205075df494a74,f7205075725f5ee4725f5ee4
121809d1041a7eb2d76809d1041809d1041
122d8d0d525noned8d0d525d8d0d525
Sheet1
Cell Formulas
RangeFormula
U111:U122U111=SUBSTITUTE(TRIM(SUBSTITUTE(CONCAT(IF(MID(","&S111&",",ROW(INDIRECT("1:"&LEN(S111)+1)),1)=",",IF(ISERROR(SEARCH(MID(","&S111&",",ROW(INDIRECT("1:"&LEN(S111)+1)),FIND(",",","&S111&",",ROW(INDIRECT("2:"&LEN(S111)+2)))-ROW(INDIRECT("1:"&LEN(S111)+1))+1),","&T111&",")),MID(","&S111&",",ROW(INDIRECT("1:"&LEN(S111)+1)),FIND(",",","&S111&",",ROW(INDIRECT("2:"&LEN(S111)+2)))-ROW(INDIRECT("1:"&LEN(S111)+1))+1),""),"")),","," "))," ",",")
V111:V122V111=LET(f_1,FILTERXML("<p><c>"&SUBSTITUTE(S111,",","</c><c>")&"</c></p>","//c"),TEXTJOIN(",",1,FILTER(f_1,ISERROR(MATCH(f_1,FILTERXML("<p><c>"&SUBSTITUTE(T111,",","</c><c>")&"</c></p>","//c"),0)),"")))
 
Upvote 0
The formula worked in 365 excel thank you for let me know i was using in office 2016, which it didn't work.

Thank you so much
 
Upvote 0
You're welcome. Thanks for the follow-up. I hope that you tried the non-volatile alternative as well. :)

Actually, I think this shorter one again should also do the job, still only with 365 version. With some further adjustment I think this could be made to work in Excel 2019 as well if that is any use.

Achyuth.xlsm
STU
110QA TagMod TagDifference
1110c103e02,9961fd48,a4b63c62,a7eb2d76,d044fd17,ea1bea7e725f5ee4,a7eb2d760c103e02,9961fd48,a4b63c62,d044fd17,ea1bea7e
1120c103e02,9961fd48,a4b63c62,a7eb2d76,cafa8afb,d044fd17,ea1bea7e9961fd48,a4b63c620c103e02,a7eb2d76,cafa8afb,d044fd17,ea1bea7e
11318915865none18915865
114344922fa71a81f31344922fa
1154f9ff4d8none4f9ff4d8
1166782f693none6782f693
1176782f693none6782f693
1186782f693none6782f693
1196782f693none6782f693
120725f5ee4,df494a74,f7205075df494a74,f7205075725f5ee4
121809d1041a7eb2d76809d1041
122d8d0d525noned8d0d525
Sheet1
Cell Formulas
RangeFormula
U111:U122U111=LET(f,FILTERXML("<p><c>"&SUBSTITUTE(S111,",","</c><c>")&"</c></p>","//c"),TEXTJOIN(",",1,IF(ISERROR(FIND(","&f&",",","&T111&",")),f,"")))
 
Upvote 0
Thanks for all the help earlier.

Below is my data which I am able to achieve using this formula.

=SUBSTITUTE(TRIM(SUBSTITUTE(CONCAT(IF(MID(","&A2&",",ROW(INDIRECT("1:"&LEN(A2)+1)),1)=",",IF(ISERROR(SEARCH(MID(","&A2&",",ROW(INDIRECT("1:"&LEN(A2)+1)),FIND(",",","&A2&",",ROW(INDIRECT("2:"&LEN(A2)+2)))-ROW(INDIRECT("1:"&LEN(A2)+1))+1),","&B2&",")),MID(","&A2&",",ROW(INDIRECT("1:"&LEN(A2)+1)),FIND(",",","&A2&",",ROW(INDIRECT("2:"&LEN(A2)+2)))-ROW(INDIRECT("1:"&LEN(A2)+1))+1),""),"")),","," "))," ",",")

on column C.

Now my question is how do I check for occurrence of top 50% of numbers under column C for each person on column D.

Data 1Data 2OutputName
1, 2, 3, 4, 51, 23,4,5Person 1
1, 3, 46, 71,3,4Person 2
1, 3, 4, 5, 85, 101,3,4,5,8Person 3
2, 5, 6, 71,3,4,6,82,5,6,7Person 4
6, 8, 9, 06, 9, 08Person 1
1, 4, 5, 6, 7, 01, 3, 5, 64,7,0Person 2
3, 5, 7, 95, 7, 93,5Person 3
2, 6, 8, 02, 56,8,0Person 4
1, 4, 5, 7, 24, 7, 21,4,5Person 1
9, 7, 5, 4, 19, 7, 15,4Person 2
3, 4, 5, 6, 73, 6, 74,5Person 3
3, 6, 8, 0, 13, 0, 16,8Person 4
1, 4, 51, 45Person 1
1, 221,2Person 2
5, 6, 76, 75,6Person 3
4, 8, 08, 04,8Person 4

Desired output using pivot

Person 14,5
Person 24,1
Person 35, 3,4
Person 48, 6

Can I use calculate field to achieve this, if yes what formula should i be using, since it contains both number and text or combination of both.
 
Upvote 0
Achyuth said:
Below is my data which I am able to achieve using this formula.
Click to expand...
Does this relate to the original question in the thread?

If so ..
  • The layout of this data is different. The sample data in post #1 contained no spaces besides the commas in columns S or T. This data does contain spaces beside the commas in both columns A and B
  • You seem to be saying that the formula in post #7 is achieving what you want in column C. If it is then I am not understanding what results you are after since the column C results contain values from column A that are not missing from column B in all the highlighted cells.
The fact that there are spaces in the data also means that the formula I suggested earlier will not work with this data. Please clarify what the actual data and requirement is.

Achyuth.xlsm
ABC
1Data 1Data 2Output
21, 2, 3, 4, 51, 23,4,5
31, 3, 46, 71,3,4
41, 3, 4, 5, 85, 101,3,4,5,8
52, 5, 6, 71,3,4,6,82,5,6,7
66, 8, 9, 06, 9, 08
71, 4, 5, 6, 7, 01, 3, 5, 64,7,0
83, 5, 7, 95, 7, 93,5
92, 6, 8, 02, 56,8,0
101, 4, 5, 7, 24, 7, 21,4,5
119, 7, 5, 4, 19, 7, 15,4
123, 4, 5, 6, 73, 6, 74,5
133, 6, 8, 0, 13, 0, 16,8
141, 4, 51, 45
151, 221,2
165, 6, 76, 75,6
174, 8, 08, 04,8
Sheet2
Cell Formulas
RangeFormula
C2:C17C2=SUBSTITUTE(TRIM(SUBSTITUTE(CONCAT(IF(MID(","&A2&",",ROW(INDIRECT("1:"&LEN(A2)+1)),1)=",",IF(ISERROR(SEARCH(MID(","&A2&",",ROW(INDIRECT("1:"&LEN(A2)+1)),FIND(",",","&A2&",",ROW(INDIRECT("2:"&LEN(A2)+2)))-ROW(INDIRECT("1:"&LEN(A2)+1))+1),","&B2&",")),MID(","&A2&",",ROW(INDIRECT("1:"&LEN(A2)+1)),FIND(",",","&A2&",",ROW(INDIRECT("2:"&LEN(A2)+2)))-ROW(INDIRECT("1:"&LEN(A2)+1))+1),""),"")),","," "))," ",",")
 
Upvote 0
I am actual using same data set, only thing I am now trying to achieve is number of names the value which has occurred against a persons name using pivot.
Since after applying this formula the data appears with comma separated value in c column. I was think how will be able to check different value occurrence in pivot
 
Upvote 0
Peter_SSs said:
Does this relate to the original question in the thread?

If so ..
  • The layout of this data is different. The sample data in post #1 contained no spaces besides the commas in columns S or T. This data does contain spaces beside the commas in both columns A and B
  • You seem to be saying that the formula in post #7 is achieving what you want in column C. If it is then I am not understanding what results you are after since the column C results contain values from column A that are not missing from column B in all the highlighted cells.
The fact that there are spaces in the data also means that the formula I suggested earlier will not work with this data. Please clarify what the actual data and requirement is.

Achyuth.xlsm
ABC
1Data 1Data 2Output
21, 2, 3, 4, 51, 23,4,5
31, 3, 46, 71,3,4
41, 3, 4, 5, 85, 101,3,4,5,8
52, 5, 6, 71,3,4,6,82,5,6,7
66, 8, 9, 06, 9, 08
71, 4, 5, 6, 7, 01, 3, 5, 64,7,0
83, 5, 7, 95, 7, 93,5
92, 6, 8, 02, 56,8,0
101, 4, 5, 7, 24, 7, 21,4,5
119, 7, 5, 4, 19, 7, 15,4
123, 4, 5, 6, 73, 6, 74,5
133, 6, 8, 0, 13, 0, 16,8
141, 4, 51, 45
151, 221,2
165, 6, 76, 75,6
174, 8, 08, 04,8
Sheet2
Cell Formulas
RangeFormula
C2:C17C2=SUBSTITUTE(TRIM(SUBSTITUTE(CONCAT(IF(MID(","&A2&",",ROW(INDIRECT("1:"&LEN(A2)+1)),1)=",",IF(ISERROR(SEARCH(MID(","&A2&",",ROW(INDIRECT("1:"&LEN(A2)+1)),FIND(",",","&A2&",",ROW(INDIRECT("2:"&LEN(A2)+2)))-ROW(INDIRECT("1:"&LEN(A2)+1))+1),","&B2&",")),MID(","&A2&",",ROW(INDIRECT("1:"&LEN(A2)+1)),FIND(",",","&A2&",",ROW(INDIRECT("2:"&LEN(A2)+2)))-ROW(INDIRECT("1:"&LEN(A2)+1))+1),""),"")),","," "))," ",",")
Click to expand...
This is made up data I have used, hence there were space, sorry about that
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,216,018
Messages
6,128,307
Members
449,439
Latest member
laurenwydo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top