Required a formula who can search given value

[Ajay45822]

We want a code/formula, who can search the numerical value in cell "E" which we put on cell "D", i mean we will first put the code/formula and after that when we put numerical value it will search the value from next cell and when it will find any one value its stop searching.

same will start from next text/number which put in cell

Excel 2010 32 bit

	A	B	C	D	E	F	G
1	It’s my first sheet
2
3		S.No.	Year		Batch
4	Entry	1	9319		3000
5		2	3219		3200
6		3	7819		8000
7		4	2419		4000
8		5	1219		2000
9		6	9319		3000
10		7	6819		8000
11		8	9419		4000
12		9	2419		4000
13		10	8319		3000
14		11	3519		5000
15		12	5619		6000

Sheet: Sheet1

after putting the number/text in column D

Excel 2010 32 bit

	A	B	C	D	E	F	G
21	Required sheet
22		S.No.	Year		Batch		only for your clarity
23	Entry	1	9319		3000
24		2	3219		3200
25		3	7819	3000,3200,8000	8000
26		4	2419		4000
27		5	1219		2000
28		6	9319	3000,2000,4000	3000	Flag	S.no. 3 value find here 3000
29		7	6819		8000
30		8	9419	4000	4000	Flag	S.no. 6 value find here 4000
31		9	2419	3000,8000,4000	4000	Flag	S.no. 8 value find here 4000
32		10	8319	3000,8000,4000	3000	Flag	S.no. 9 value find here 3000
33		11	3519		5000
34		12	5619		6000

Sheet: Sheet1

[AliGW]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[Ajay45822]

pls check and update now

[Glenn Kennedy]

Your thread title is STILL not great. But you have made the effort. i will change it again, once I understand your requirement. Can you attach an Excel sheet, please. Also, i am not sure exactly what you want to see and where you want to see it. Your description is (to me!!) rather confusing.

[PaulM100]

He probably wants to concat the Batch no based on Year if flagged, but his data is odd.

[Ajay45822]

ok i am explain it

its my first sheet

Excel 2010 32 bit

	A	B	C	D	E	F	G
1	It’s my first sheet
2
3		S.No.	Year		Batch
4	Entry	1	9319		3000
5		2	3219		3200
6		3	7819		8000
7		4	2419		4000
8		5	1219		2000
9		6	9319		3000
10		7	6819		8000
11		8	9419		4000
12		9	2419		4000
13		10	8319		3000
14		11	3519		5000
15		12	5619		6000

Sheet: Sheet1

We want a code/formula, who can search the numerical/text value in cell E, search value will be from Column D,

i mean we will first put the code/formula and after that when we will put numerical value in column D, formula will search the value from next cell and when it will find any one value its show "Flag" and its stop searching that value.

after that we will put another value and same process will start.

now i am putting first value

Excel 2010 32 bit

	A	B	C	D	E	F	G
1	Required sheet
2
3		S.No.	Year		Batch		only for your clarity
4	Entry	1	9319		3000
5		2	3219		3200
6		3	7819	3000,3200,8000	8000
7		4	2419		4000
8		5	1219		2000
9		6	9319		3000	Flag	S.no. 3 value find here 3000
10		7	6819		8000
11		8	9419		4000
12		9	2419		4000
13		10	8319		3000
14		11	3519		5000
15		12	5619		6000

Sheet: Sheet1

its my first result, now we are putting next value/text in column D

Excel 2010 32 bit

	A	B	C	D	E	F	G
1	Required sheet
2
3		S.No.	Year		Batch		only for your clarity
4	Entry	1	9319		3000
5		2	3219		3200
6		3	7819	3000,3200,8000	8000
7		4	2419		4000
8		5	1219		2000
9		6	9319	3000,2000,4000	3000	Flag	S.no. 3 value find here 3000
10		7	6819		8000
11		8	9419		4000	Flag	S.no. 6 value find here 4000
12		9	2419		4000
13		10	8319		3000
14		11	3519		5000
15		12	5619		6000

Sheet: Sheet1

hope now everything clear

[Ajay45822]

He probably wants to concat the Batch no based on Year if flagged, but his data is odd.

no, i will put the data on d

for example, if i post on D6, then formula will search the D6 value from E7 and when it any one value it will show flag and stop that search.

[Glenn Kennedy]

I know what you want. The way to go is probably using FILTERXML. However, I am still struggling to understand how it works!!

[Ajay45822]

I know what you want. The way to go is probably using FILTERXML. However, I am still struggling to understand how it works!!

ok, waiting for a solution

[Glenn Kennedy]

This is not working... yet. However, the 2 best people to sort this out are in Singapore & Canada. I still don't really understand how the function works. I am close... but not there yet. So, I will send them a message to take a look at this post.

Of course, if others, familiar with the function - or who have another suggestion - feel free to jump in.

I am a bit above my pay grade here.

=IF(SUMPRODUCT((--ISNUMBER(SEARCH(FILTERXML("<a><b>"&SUBSTITUTE(LOOKUP(2,1/($D4:D$5<>""),$D4:D$5),",","</b><b>")&"</b></a>","//b"),$E5))))>0,"Flag","")

[Glenn Kennedy]

There was an error in my non-working formula. Still not working, but should have been:
=IF(SUMPRODUCT((--ISNUMBER(SEARCH(FILTERXML("<a><b>"&SUBSTITUTE(LOOKUP(2,1/($D4:D$5<>""),$D$5:D5),",","</b><b>")&"</b></a>","//b"),$E5))))>0,"Flag","")

[Ajay45822]

pls update when you get update on this code

[Glenn Kennedy]

Hey! You're in Agra. I spent 3 wonderful months in India in 2014 & 2015 travelling round the N and S. Great place. The Taj is really special.

[Ajay45822]

There was an error in my non-working formula. Still not working, but should have been:
=IF(SUMPRODUCT((--ISNUMBER(SEARCH(FILTERXML("<a><b>"&SUBSTITUTE(LOOKUP(2,1/($D4:D$5<>""),$D$5:D5),",","</b><b>")&"</b></a>","//b"),$E5))))>0,"Flag","")

Thanks for your efforts and help,
some more information for you,
(1) We have approximate 300 rows value
(2) In Column D, there will be 3 text/numbers or may be it 4 or 5

Again thanks for your efforts

[Ajay45822]

Hey! You're in Agra. I spent 3 wonderful months in India in 2014 & 2015 travelling round the N and S. Great place. The Taj is really special.

your welcome again.............

Visit Taj and India again

[Glenn Kennedy]

OK. That won't matter.

[CK76]

Hmm, you don't need FILTERXML for this. It's redundant to Lookup(2,1/Search()) part.

This alone will give same result as Glenn's formula.
=IF(ISERROR(LOOKUP(2,1/SEARCH(E5,$D$4:D4),$D$4:D4)),"","Flag")

Difficulty is that you need to exclude G11 result.

If you are ok with flag other than "Flag"...
=IF(COUNTIF($J4:J4,LOOKUP(2,1/SEARCH(E5,$D$4:D4),$D$4:D4))>0,"",IFERROR(LOOKUP(2,1/SEARCH(E5,$D$4:D4),$D$4:D4),""))

Copy down.

See attached.

EDIT:
Note that FILTERXML is only needed when you need to extract specific sub-string based on delimiter position. In this instance, you only need to check that sub-string exists, so Search/Find is sufficient.

[Ajay45822]

Many Thanks "CK76", got the part result of my query. Code in Cell H are working fine but Cell G formula is not working.

Still there is some problem, Flag option is not working if we change the data in "Column D"
your code has solved my query but something is still missed. pls check the following point:-

1. We have put the data on D7 (data is 3000,3200,8000), now we will search it from E8 and when we find the any value from three numbers (Numbers are 3000,3200,8000) code will stop the searching after putting a "Flag"
2. Now we will put the Data on D10 (Data is 3000,2000,4000) and code will start the searching from E11 and when we find the any value from three numbers (Numbers are 3000,2000,4000) code will stop the searching after putting a "Flag"
3. Now we will put the Data on D12 (Data is 3000,8000,4000) and code will start the searching from E13 and when we find the any value from three numbers (Numbers are 3000,8000,4000) code will stop the searching after putting a "Flag"

i mean when we find the searched data, we will stop previous searching and will put new data on the cell where we have find the match and that we start the search from next cell

please check the above points and provide the code if possible.

You have helped me a lot and saved my time. thank you very much CK76

[Ajay45822]

You have used a great quote, i like it realy..
“Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
― Robert A. Heinlein

[Glenn Kennedy]

Ajay. A picture is worth 1000 words and an Excel sheet is worth 1000 pictures. Update your sheet and show what's wrong.

[menem]

If you don't mind to use many of helper cells. Please review this file (Sheet1 (2)).

Formula & Calculation steps.

T3 finding last row of data

Please Login or Register  to view this content.

R4 indicate row number (avoid OFFSET that become array value if use ROW() directly in formula)

Please Login or Register  to view this content.

L4 finding row that match value for each element (in this file allow 5 elements)

Please Login or Register  to view this content.

K4 the lowest row (1st found)

Please Login or Register  to view this content.

H4 trace back for founder value

Please Login or Register  to view this content.

Regards.

[Ajay45822]

Thanks menem Ji, you have used a solid logic and help me, really great

you saved lot of my time....

the formula is working fine for me, can we do that without helper formula,

Heartily thanks to you

[Ajay45822]

Ajay. A picture is worth 1000 words and an Excel sheet is worth 1000 pictures. Update your sheet and show what's wrong.

please check, i have updated the sheet and no "Flag" are coming

[menem]

Sorry Ajay, it's too difficult for me to do without helper formula cells. >_<'


Regards.

[CK76]

Can't think of way to do it with formula alone in Excel 2010 (without use of helper column)
. You could with Office 365 with newly introduced array functions (Filter, Unique etc).

Perhaps, you can do it by combining formula & custom number format.
In G5:
=IF(COUNTIF($G4:G4,LOOKUP(2,1/SEARCH(E5,$D$4:D4),ROW($D$4:D4)))>0,0,IFERROR(LOOKUP(2,1/SEARCH(E5,$D$4:D4),ROW($D$4:D4)),""))

Copy down. If it doesn't work as is, you may need to confirm as array: (CTRL + SHIFT + ENTER)

Then select range and apply following number format.

Please Login or Register  to view this content.

See attached. Note that it only changes display, and underlying value stored is still numeric.

[Ajay45822]

Thank you very much CK76.

thanks for helping us.

[Ajay45822]

Sorry Ajay, it's too difficult for me to do without helper formula cells. >_<'


Regards.

Thanks menem JI,

thanks for giving me time and support by providing the formula.

[CK76]

You are welcome

If you are satisfied with the solution provided. Please mark the thread as solved using thread tools found at top of your initial post.

[Ajay45822]

Your Post #25 is great work

only one problem in that we need to find only one number any of them from three, and after that formula will not search that three number but in attached file its still searching after finding the one value

Formula will search when we add new value in next cell

pls check sheet1 and sheet2 (we need the result as heading Expected in sheet1 & sheet3), luckily in sheet2 result are same as expected

pls check and help

[Ajay45822]

if its possible, pls solve or we need to create a new post for this modification

[AliGW]

Keep it here, please - it's just a modification you are asking for.

You keep asking about this. The rule of thumb is:

1. Completely new request (significantly different to the thread title) = new thread.
2. Follow-on question requiring a small adaptation or modification = same thread.

[CK76]

There is typo in post #25. $G4 should be $G$4

Please Login or Register  to view this content.

Otherwise, reference will move down with row positions and will only look at current row (rather than extending from G4 down).

[Ajay45822]

Thanks bro for sorting the problem and help.

Thanks

[CK76]

You are welcome and thank for the rep

[Ajay45822]

there is problem in formula, please check G8, its showing flag but value not matched

[JeteMc]

I am going to suggest a modification to the proposal I made for the attachment, Book1(Ajay45822), in post #15 of your thread "Formula or VBA - who can check the Flag and add three values"
Modify the formula in column D to read:

Please Login or Register  to view this content.

Let us know if you have any questions.

[Ajay45822]

Thanks JeteMc, you have a great knowledge

thanks for helping us and other persons query

Thanks

[CK76]

@Ajay45822

The issue is that, 3 is partial match to "3000,3200,8000". From your initial sample, I assumed that all batch# are 4 char length.

To avoid partial match... you could concatenate "," to front and end of values in D column. and search for ","&Value in E column&",".

So formula then becomes...

Please Login or Register  to view this content.

[Ajay45822]

Hi JeteMc,

tried the changes in column D but now working, can you please check once again

[JeteMc]

Formula is applied in the attached file.
Let us know if you have any questions.

[Ajay45822]

Thanks Bro,

its working,

Thanks for help.

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.