• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Returning a string based on criteria

dwrowe001

Member
Hello Everyone,

I need some help developing a Macro which will find matches to my criteria, then return the line right below the matches.

For example I have a list (Example spreadsheet attached) with 4 columns of data. First two columns are names. The third column are days of the week and the forth column are the numbers 1 thru 4 in random order. I am constantly adding names, the day of the week and a random number to the bottom of the list each day. All my searches are based on the last entry to the list (from the bottom) up.. so say I want to search on the last 3 entries, which in this case would the random numbers 4,2,1. I would like to find all occurrences of 4,2,1 in the list (Sheet 1) in the example. There are 3 occurrences of 4,2,1 found in this list Hilighted in yellow. I need the very next entry after each of the 3 occurrences to be returned under Results, Hilighted light Blue.

My list has hundreds of entires.. and multiple columns A,B,C,D I would like the ability to be able to search on any of the columns, going back any number of entries from the bottom. I am adding names, days of the week and random numbers 1 thru 4 to the list daily. So the Macro should take that into account. There is another example on Sheet 2.

One last request, it would be great if there could be a button to reset/clear the search criteria and results.

I would greatly appreciate any help and Thank you all in advance.

Dave
 

Attachments

  • Help.xlsx
    18.6 KB · Views: 4
vletm,
Yes, that works perfect... thank you!!!!
If I may, can I request a couple modifications to it?

See the attached file, sheet 2 of attached file for details.. just a couple additions if possible.

Also, can you tell me where the code is? I couldn't find it? I am going to need to copy it over to another workbook.

Thank you again,
Dave
 

Attachments

  • Help-2.xlsb
    24.2 KB · Views: 1
dwrowe001
Something like this?

'codes' ...
1) move mouse on button
2) press right button
3) select 'Assign Macro...'
4) There ...
 

Attachments

  • Help-2.xlsb
    27.1 KB · Views: 3
vletm,
yeah, that works great also, but what I was wanting was for each column to match at the same time.. make sense?

for example A and B to match together at the same time, and if they did then return the next line.
A B
Mindy Dennis
Cathy Tom

however, I like the way you have it working now though..

At the risk of wearing out my good fortunes with you, wonder if it could be set up with two separate search buttons, one button would search for when the columns match separately (the way it's working now) and a second search button for when I wanted to search having the columns match at the same time? Does this make sense.. If this is too much, don't worry about it, I am pleased with how it's working now.

Thank you a thousand times for taking the time and efforts doing this for me.
Dave
 
Vletm,

Dang it, I thought I could get by with giving you 1 column thinking I could make simple changes to the code by adding and changing column letters etc… but this one it to complicated and I can’t seem to get it to work….

I have 5 groups of data that I need to be able to search.. and I thought I could modify it myself… I have in the past with simpler code but not this one… I tried and it didn’t work.

Ignore but leave column A.

Attached is what I have… the other 4 columns are like the one I have sent you. I need to be able to search on all the other ones also… just need to add the new columns. And I totally forgot about column Z which needs to be added also. I wish I knew how to do this myself…. I’m sorry.. I thought I could make the changes myself but can’t. Hope this doesn’t cause a problem for you?

Dave
 

Attachments

  • Help-2-2.xlsb
    23.6 KB · Views: 1
dwrowe001 ... hmm?
> this layout ...
> no matter number of groups
> Ignore but leave column A. ... hmm? ... maybe something or not?
> I totally forgot about column Z which needs to be added also ..
hmm? do forgot mean as same as now remember?
and where to add ... where?
> problem ... who would like to have problem?
... it's better to have challenge
 

Attachments

  • Help-2-2.xlsb
    27.2 KB · Views: 3
vletm,

I really appreciate your efforts and willingness to tackle this challenge..

I need to keep column layout regarding placement of columns.. Column A must remain with the numbers 1 - infinity. See attachment.

GP1 must be columns B-E, GP2 must be columns G-J, GP3 in Columns L-O, GP4 in Q-T, GP5 in V-Y. And Column Z must remain with True/False. And the search buttons, criteria and Results can stay where they are.

you had it working perfectly at first, I think it was your response post #2 . I need to be able to search each of the columns B thru Z

the way you had it working with attachment in post #2. And I need to keep the columns arranged as above.

I know you've been more then accommodating with me and my requests, I'm really grateful for this, as I've been more then
confusing with my requests thus far.

Thank you very much.
Dave
 

Attachments

  • Help-2-2.xlsb
    24.3 KB · Views: 1
dwrowe001
Your the latest layout 'not so useful to extend ... but ...'
... swapped back ...
added some 'gotta follow hints' or ... this would work other way!
 

Attachments

  • Help-2-2 (1).xlsb
    26.4 KB · Views: 4
Hi vletm,
what do you mean when you say I can't write anything in those cells? I need to copy the code over to another spreadsheet file which has more then just 7 lines of data / names in it.
 
Vletm,

I’m sorry for confusing you.. it’s hard for me to try to explain exactly how I want it to work.

As you have it working now, I can only type in and search columns A, B, C, or D. I don’t need to search the A column. I know my original posts Had only columns A, B, C, and D. I corrected my error and told you about the other columns.

I don’t need to search Column A.

What I need is to be able to search on either columns B, C, D, E or G, H, I, J, or L, M, N, O or Q,R, S, T, or V,W, X, Y. and also the Z column, Individually.. meaning, the search will be only in one particular column, which ever one I select in the “Col” cell.

Example, if I enter in the Search Col Cell, “G” and then 2 in the “Back” cell. The Search would be limited to only the G column. Not expanded across to the other columns.

But I need to be able to this for all the columns, B thru Z.

I’m sorry for confusing you with my needs.. I hope this clarifies things?

Please let me know.

Thank you

Dave
 
dwrowe001
a) 'yellow box' ... You had 'data' in top of sheet .. okay?
and below that You wrote 'some notes' .. okay?
BUT those 'Your notes' would use as data!
= if You need to write 'comments' somewhere, then You gotta write those to the right side of [clear]-button.
You can add as many data rows as You need.
Every 'GPn' can have different number of rows.
... cell 'Z1' have to left EMPTY!

b) If You write 'this' and if You mean 'that' then You will get 'this'.

So far, there can 'search' from four columns, as You have written:
the 1st and 2nd columns has 'names',
3rd column has 'weekday' and 4th column has 'number'
If You write 'A' below of Col, then this would search from columns B,G,L,Q& V.
If You write 'B' below of Col, then this would search from columns C,H,M,R,W.
... and so on
= 'A' is the 1st column of GP
= 'B' is the 2nd column of GP
= 'C' is the 3rd column of GP
= 'D' is the 4th column of GP

And now You wrote:
But I need to be able to this for all the columns, B thru Z.
> That won't work well! ... there are some empty columns!

... and even now, You can see those 'Results' in each 'GP's!
Isn't that what are You looking for?
 
dwrowe001
Column letters are hidden!

Next version's instructions:
1) Apply below 'Back' number!
2) Press [Select]-btn (yellow comes)
( while 'Select-mode' You would ONLY make 'selections'! )
You can 'reset' 'Select-mode' by press [ Clear ]-btn
3) Select ANY columns to search by click any cell in that column.
eg if You need to work with GP1's MINDY then click any cell in that column
> You will see yellow cell below 'GPn' <
4) Repeat #3 as many times as needed
5) Press [ Search ] to get 'Results'
 

Attachments

  • Help-2-2 (2).xlsb
    28.7 KB · Views: 4
Back
Top