Complicated formula or macro

[Guest]

Is there a macro that does a search in different sheets for you? Here is the
complicated scenario I am trying to create: Let's say in Sheet 1, I have a
cell with the number 305212344. Now, through minor analysis, we know that
the number 305 is the signifier for this number. Meaning, once the excel
realizes that it is looking for 305 it then serches the 5 other data sheets
for the 305 data. Once it finds the 305 data, it then looks at the data for
305 and looks at the remaining numbers of 212344 and tries to find the number
21 in this data (the number can vary in length). Once it finds the 21 it
looks at the length of the remaining numbers (2344 in this case) and sees if
the length matches the length of digits given for 21. As mentioned the second
search, the number length may vary, so that instead of 21, it may be looking
for 2 so 12344 is left and it counts those digits (5) and checks to see if
that number matches to the number of digits required by 2.

Again, I know this is complicated, but I wanted to see if there was anyway
possible of doing this. Thanks in advance.

 

[Guest]

You will need a macro and a VERY clear set of rules. The obvious one from
your example is how is it decided whether it is 2 or 21 that is required.

 

[Ron Rosenfeld]

Is there a macro that does a search in different sheets for you? Here is the
complicated scenario I am trying to create:

I'm sure there are macros that will search in different sheets for you.

However, both in this and in another, related thread of yours, you have yet to
set out the rules for this macro to follow in sufficient detail to allow a
macro to be constructed that will accomplish your scenario.

For example:

Now, through minor analysis, we know that the number 305 is the signifier for this number.

"We" may know that, but how does your macro know that?

The same applies to most of your other specifications. You have mentioned
criteria for this macro to follow, but the criteria are not fixed but rather
depend on a data source which you do not specify.

In general, you could do something like:


Number = [cell containing value on sheet 1].value
Signifier = InputBox("Input Signifier")

For each ws in ThisWorkbook
for each cell in ws
if left(Number,len(signifier)) = Signifier then
Temp = right(Number,len(Number)-Len(signifier))

and then input something else so the macro knows whether to look for 2, 21,
first digit, first two digits, etc.


--ron

 

[Jef Gorbach]

You will need a macro and a VERY clear set of rules. The obvious one from
your example is how is it decided whether it is 2 or 21 that is required.

This could be a fun macro to code.
in general,
book: Mr Excel on Excel has a macro to search all sheets (the standard Find
only searches the current sheet)
gather all the 305 rows in a new sheet
trim off the leading 305
loop data
select case
case 2: if len(correct) then process
case 21: if len(correct) then process
end case
next