Search for input criteria, return designated cells.
Solved/Closed
crlarus
Posts
7
Registration date
Friday August 11, 2017
Status
Member
Last seen
August 18, 2017
-
Updated on Aug 18, 2017 at 09:32 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Aug 19, 2017 at 04:09 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Aug 19, 2017 at 04:09 AM
Related:
- Search for input criteria, return designated cells.
- How to input @ in laptop - Guide
- Lg tv change input name ✓ - Smart TV Forum
- Based on the values in cells b77:b81, what function can automatically return the value in cell c77? ✓ - Excel Forum
- Input phone password - Phones, PDA & GPS Forum
- Yahoo search history - Guide
9 responses
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Aug 12, 2017 at 08:02 AM
Aug 12, 2017 at 08:02 AM
Hello Crlarus,
We'll more than likely need to see a sample of your work book in order to be able to help you out. Please upload a sample (please use dummy data) to a free file sharing site such as GE.TT or Drop Box and then post the link to your file back here. Please also show us what code you have tried to use.
I'm assuming that you would like to select a criteria from, perhaps, a drop down or a search box or an input box in the destination worksheet, find this criteria in a designated column in the index sheet and then transfer the relevant rows of data back to the destination sheet. The data will be sourced from non-contiguous columns. Is this correct?
Following is the link to an example I did for another Poster over a year ago. See if this is at least close to what you are wanting to do:-
http://ge.tt/3zVY29m2
On sheet1, there is a short description of what the code does.
Let us know your thoughts.
Cheerio,
vcoolio.
We'll more than likely need to see a sample of your work book in order to be able to help you out. Please upload a sample (please use dummy data) to a free file sharing site such as GE.TT or Drop Box and then post the link to your file back here. Please also show us what code you have tried to use.
I'm assuming that you would like to select a criteria from, perhaps, a drop down or a search box or an input box in the destination worksheet, find this criteria in a designated column in the index sheet and then transfer the relevant rows of data back to the destination sheet. The data will be sourced from non-contiguous columns. Is this correct?
Following is the link to an example I did for another Poster over a year ago. See if this is at least close to what you are wanting to do:-
http://ge.tt/3zVY29m2
On sheet1, there is a short description of what the code does.
Let us know your thoughts.
Cheerio,
vcoolio.
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Aug 15, 2017 at 07:37 AM
Aug 15, 2017 at 07:37 AM
Hello Crlarus,
Apparently your last reply was trapped by our spam robot because it picked up a couple of words which are regularly used in spam.
However, I've sorted that out and will have a good look at your sample tomorrow.
Just briefly. We could use cell B8 as the input/search box using the account numbers as the identifiers if you like. Using the account numbers would be the safest bet. Yes, if nothing is returned then either there isn't any data to search for or, as you say, human error has buggered things up for you.
I am also assuming that you would like Columns C:G in the Destination sheet populated with data from Columns A, D:E, G and L from the PlaceHolderSample sheet. The remaining columns(H:S) will be left for the formulae to do their job.
I'll be in touch before long.
Cheerio,
vcoolio.
Apparently your last reply was trapped by our spam robot because it picked up a couple of words which are regularly used in spam.
However, I've sorted that out and will have a good look at your sample tomorrow.
Just briefly. We could use cell B8 as the input/search box using the account numbers as the identifiers if you like. Using the account numbers would be the safest bet. Yes, if nothing is returned then either there isn't any data to search for or, as you say, human error has buggered things up for you.
I am also assuming that you would like Columns C:G in the Destination sheet populated with data from Columns A, D:E, G and L from the PlaceHolderSample sheet. The remaining columns(H:S) will be left for the formulae to do their job.
I'll be in touch before long.
Cheerio,
vcoolio.
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Aug 16, 2017 at 03:59 AM
Aug 16, 2017 at 03:59 AM
Hello Crlarus,
Ha. Roberta our robot is a little like an old Head Mistress I had the pleasure(?) of knowing back in my school days. Too literal!
Anyway, I think that the following code may help you out:-
Enter an account number in cell B8 in the destination sheet and the code will filter the source sheet for that account number and then transfer all the rows of data associated with the account number to the destination sheet but only the data from columns A, D:E, G and L will be transferred to columns C:G in the destination sheet.
Following is the link to your work book with the code implemented:-
http://ge.tt/4GWNSBm2
Click on the "Account Search" button to see it work.
I hope that this helps.
Cheerio,
vcoolio.
Ha. Roberta our robot is a little like an old Head Mistress I had the pleasure(?) of knowing back in my school days. Too literal!
Anyway, I think that the following code may help you out:-
Sub FindDetails() Dim lr As Long Dim ws As Worksheet Dim ws1 As Worksheet Dim ASearch As String Set ws = Sheet1 Set ws1 = Sheet2 AccSearch = ws1.[B8].Value Application.ScreenUpdating = False ws.Range("F1", ws.Range("F" & ws.Rows.Count).End(xlUp)).AutoFilter 1, AccSearch lr = ws.Range("E" & Rows.Count).End(xlUp).Row If lr > 1 Then Union(ws.Range("A2:A" & lr), ws.Range("D2:E" & lr), ws.Range("G2:G" & lr), ws.Range("L2:L" & lr)).Copy ws1.Range("C" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues End If ws.[F1].AutoFilter Application.CutCopyMode = False Application.ScreenUpdating = True End Sub
Enter an account number in cell B8 in the destination sheet and the code will filter the source sheet for that account number and then transfer all the rows of data associated with the account number to the destination sheet but only the data from columns A, D:E, G and L will be transferred to columns C:G in the destination sheet.
Following is the link to your work book with the code implemented:-
http://ge.tt/4GWNSBm2
Click on the "Account Search" button to see it work.
I hope that this helps.
Cheerio,
vcoolio.
crlarus
Posts
7
Registration date
Friday August 11, 2017
Status
Member
Last seen
August 18, 2017
Aug 16, 2017 at 11:07 AM
Aug 16, 2017 at 11:07 AM
Hey Vcoolio,
This is phenomenal. I will apply it to the actual sheets and see how it runs against the full set of data, but I expect I should not run into any issues. I cannot express my gratitude enough; more importantly, I hope I can learn from it for future endeavors as well.
Thanks so much and best regards,
crlarus
This is phenomenal. I will apply it to the actual sheets and see how it runs against the full set of data, but I expect I should not run into any issues. I cannot express my gratitude enough; more importantly, I hope I can learn from it for future endeavors as well.
Thanks so much and best regards,
crlarus
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Updated on Aug 17, 2017 at 04:50 AM
Updated on Aug 17, 2017 at 04:50 AM
Hello Crlarus,
You're welcome. Glad that I was able to help.
There's one thing that I overlooked. I didn't ask if you wanted Columns C:G in the destination sheet cleared once you were finished with the data of a particular account and before the next lot of data from the next account is transferred across. As the code stands now, it will continue to append new data to the bottom of the previous each time that you click on the button.
This can be arranged if you like.
Cheerio,
vcoolio.
You're welcome. Glad that I was able to help.
There's one thing that I overlooked. I didn't ask if you wanted Columns C:G in the destination sheet cleared once you were finished with the data of a particular account and before the next lot of data from the next account is transferred across. As the code stands now, it will continue to append new data to the bottom of the previous each time that you click on the button.
This can be arranged if you like.
Cheerio,
vcoolio.
crlarus
Posts
7
Registration date
Friday August 11, 2017
Status
Member
Last seen
August 18, 2017
Aug 17, 2017 at 08:39 AM
Aug 17, 2017 at 08:39 AM
Hey Vcoolio,
That suggestion would make it cleaner and again would be appreciated. I was just deleting the pulls and relocating them as needed. What would be the addendum to the macro string?
Thanks,
crlarus
That suggestion would make it cleaner and again would be appreciated. I was just deleting the pulls and relocating them as needed. What would be the addendum to the macro string?
Thanks,
crlarus
Didn't find the answer you are looking for?
Ask a question
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Aug 18, 2017 at 06:10 AM
Aug 18, 2017 at 06:10 AM
Hello Crlarus,
You weren't that far off actually.
I've taken it a little further and worked it so that you can use both codes for the same purpose. So, if you only have a meter number to work off, then using that number, you can still transfer all the required data just like the first code would do using the account number.
So, the two (similar) codes are:-
and
The code "FindDetails" is the same one as previous but now also clears the data from the destination sheet before new data is transferred across.
You should be able to pick up the minor additions.
The code "FindDetails2" is similar but searches for the meter number entered into J3 instead and then transfers the relevant data to the destination sheet as well. The relevant account number (if found) appears in J4 as well as appearing in B8.
BTW, don't use the same subroutine name for more than one code in the same project as an error will be thrown up.
Here is the link to the updated version:-
http://ge.tt/7orqzCm2
Play around with it and see if it works for you.
Test it in a copy of your work book first.
I hope that this helps.
Cheerio,
vcoolio.
You weren't that far off actually.
I've taken it a little further and worked it so that you can use both codes for the same purpose. So, if you only have a meter number to work off, then using that number, you can still transfer all the required data just like the first code would do using the account number.
So, the two (similar) codes are:-
Sub FindDetails() Dim lr As Long Dim lr1 As Long Dim ws As Worksheet Dim ws1 As Worksheet Dim AccSearch As String Set ws = Sheet1 Set ws1 = Sheet2 lr1 = ws1.Range("H" & Rows.Count).End(xlUp).Row AccSearch = ws1.[B8].Value Application.ScreenUpdating = False ws1.Range("C9:G" & lr1).ClearContents ws.Range("F1", ws.Range("F" & ws.Rows.Count).End(xlUp)).AutoFilter 1, AccSearch lr = ws.Range("E" & Rows.Count).End(xlUp).Row If lr > 1 Then Union(ws.Range("A2:A" & lr), ws.Range("D2:E" & lr), ws.Range("G2:G" & lr), ws.Range("L2:L" & lr)).Copy ws1.Range("C" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues End If ws.[F1].AutoFilter ws1.[J3:J4].ClearContents ws1.[B8].Select Application.CutCopyMode = False Application.ScreenUpdating = True End Sub
and
Sub FindDetails2() Dim lr As Long Dim lr1 As Long Dim ws As Worksheet Dim ws1 As Worksheet Dim MeterSearch As String Set ws = Sheet1 Set ws1 = Sheet2 lr1 = ws1.Range("H" & Rows.Count).End(xlUp).Row MeterSearch = ws1.[J3].Value Application.ScreenUpdating = False ws1.Range("C9:G" & lr1).ClearContents ws.Range("G1", ws.Range("G" & ws.Rows.Count).End(xlUp)).AutoFilter 1, MeterSearch lr = ws.Range("E" & Rows.Count).End(xlUp).Row If lr > 1 Then Union(ws.Range("A2:A" & lr), ws.Range("D2:E" & lr), ws.Range("G2:G" & lr), ws.Range("L2:L" & lr)).Copy ws1.Range("C" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues ws.Range("F2:F" & lr).Copy ws1.[XFD1] ws1.[J4] = ws1.[XFD1] ws1.[B8] = ws1.[XFD1] End If ws.[G1].AutoFilter ws1.[J4].Select Application.CutCopyMode = False Application.ScreenUpdating = True End Sub
The code "FindDetails" is the same one as previous but now also clears the data from the destination sheet before new data is transferred across.
You should be able to pick up the minor additions.
The code "FindDetails2" is similar but searches for the meter number entered into J3 instead and then transfers the relevant data to the destination sheet as well. The relevant account number (if found) appears in J4 as well as appearing in B8.
BTW, don't use the same subroutine name for more than one code in the same project as an error will be thrown up.
Here is the link to the updated version:-
http://ge.tt/7orqzCm2
Play around with it and see if it works for you.
Test it in a copy of your work book first.
I hope that this helps.
Cheerio,
vcoolio.
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Aug 19, 2017 at 04:09 AM
Aug 19, 2017 at 04:09 AM
Hello Crlarus,
Excellent! I'm glad that both solutions worked for you and, again, I'm happy to have been able to help.
No, there aren't feedback forms that can be filled in. There is just a "Helpful" box at the top left of each post which you can add a plus or a minus to.
Anyway, you being thankful and appreciative for any help is enough for me as would any Volunteer here at CCM appreciate a simple "thank you" for any help offered.
I hope that your work burden has now been reduced somewhat!
Take care.
Cheerio,
vcoolio.
Excellent! I'm glad that both solutions worked for you and, again, I'm happy to have been able to help.
No, there aren't feedback forms that can be filled in. There is just a "Helpful" box at the top left of each post which you can add a plus or a minus to.
Anyway, you being thankful and appreciative for any help is enough for me as would any Volunteer here at CCM appreciate a simple "thank you" for any help offered.
I hope that your work burden has now been reduced somewhat!
Take care.
Cheerio,
vcoolio.
crlarus
Posts
7
Registration date
Friday August 11, 2017
Status
Member
Last seen
August 18, 2017
Aug 15, 2017 at 10:26 AM
Aug 15, 2017 at 10:26 AM
Hey Vcoolio,
I can't imagine what words I used actually relegated my comment to spam (haha). I have been trying to tailor the previous example to my needs but to no avail. Regarding the task at hand, everything you mentioned seems exactly what I am trying to accomplish. The account numbers would be the identifier, and the respective headings between index to destination would be populated with the corresponding data. I will reiterate that I very much appreciate your time and effort. I look forward to hearing and learning from your input here shortly.
Kindest regards,
crlarus
I can't imagine what words I used actually relegated my comment to spam (haha). I have been trying to tailor the previous example to my needs but to no avail. Regarding the task at hand, everything you mentioned seems exactly what I am trying to accomplish. The account numbers would be the identifier, and the respective headings between index to destination would be populated with the corresponding data. I will reiterate that I very much appreciate your time and effort. I look forward to hearing and learning from your input here shortly.
Kindest regards,
crlarus
crlarus
Posts
7
Registration date
Friday August 11, 2017
Status
Member
Last seen
August 18, 2017
Updated on Aug 18, 2017 at 04:40 AM
Updated on Aug 18, 2017 at 04:40 AM
Hey Vcoolio,
There may be a potential for a second module with the same data. I am attempting to include a reverse account number look-up should a potential input only be a meter number. I have been trying to manipulate the successful macro provided but realize I must need a different sub-routine altogether. So, the string would Find the specified value, search the index for the first appearance of the value, and copy and paste the first iteration of the account number.
So far for the second module with no success, the adaptation from the first module is as follows:
Sub FindDetails()
Dim lr As Long
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim ASearch As String
Set ws = Sheet1
Set ws1 = Sheet2
MeterSearch = ws1.[J3].Value
Application.ScreenUpdating = False
ws.Range("G1", ws.Range("G" & ws.Rows.Count).End(xlUp)).AutoFilter 1, AccSearch
lr = ws.Range("E" & Rows.Count).End(xlUp).Row
If lr > 1 Then
ws.Range("G2:G").Copy
ws1.Range("J4").End(3)(2).PasteSpecial xlPasteValues
End If
ws.[F1].AutoFilter
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Obviously, it is not working. Below is a link with the formatted changes and attempt for Module 2 shown above.
Thanks so much,
crlarus
There may be a potential for a second module with the same data. I am attempting to include a reverse account number look-up should a potential input only be a meter number. I have been trying to manipulate the successful macro provided but realize I must need a different sub-routine altogether. So, the string would Find the specified value, search the index for the first appearance of the value, and copy and paste the first iteration of the account number.
So far for the second module with no success, the adaptation from the first module is as follows:
Sub FindDetails()
Dim lr As Long
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim ASearch As String
Set ws = Sheet1
Set ws1 = Sheet2
MeterSearch = ws1.[J3].Value
Application.ScreenUpdating = False
ws.Range("G1", ws.Range("G" & ws.Rows.Count).End(xlUp)).AutoFilter 1, AccSearch
lr = ws.Range("E" & Rows.Count).End(xlUp).Row
If lr > 1 Then
ws.Range("G2:G").Copy
ws1.Range("J4").End(3)(2).PasteSpecial xlPasteValues
End If
ws.[F1].AutoFilter
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Obviously, it is not working. Below is a link with the formatted changes and attempt for Module 2 shown above.
Thanks so much,
crlarus
crlarus
Posts
7
Registration date
Friday August 11, 2017
Status
Member
Last seen
August 18, 2017
Aug 18, 2017 at 10:19 AM
Aug 18, 2017 at 10:19 AM
Hey Vcoolio,
This was a phenomenal help, and I hope I can learn from all that you have provided (you definitely made it seem so much more attainable). I have applied and troubleshot the new modules to the complete data set with no errors. I cannot express enough gratitude for your time since this is going to expedite a significant amount of leg work in our project for the coming months.
Should there be some sort of feedback report please let me know where I can fill one out to provide a stellar review. It was a pleasure conversing with you here.
Very much thanks and deepest regards,
crlarus
This was a phenomenal help, and I hope I can learn from all that you have provided (you definitely made it seem so much more attainable). I have applied and troubleshot the new modules to the complete data set with no errors. I cannot express enough gratitude for your time since this is going to expedite a significant amount of leg work in our project for the coming months.
Should there be some sort of feedback report please let me know where I can fill one out to provide a stellar review. It was a pleasure conversing with you here.
Very much thanks and deepest regards,
crlarus
Aug 14, 2017 at 10:13 AM
Firstly, thank you for your time and response. To answer your clarifying questions, the data will be sourced from non-contiguous columns and too many account numbers to legitimize a drop down select option (full set is about 19,000 Accounts and a total of 660,000 metric reads with corresponding dates). I have not explored the idea of a search bar, but for now, simply an input cell will suffice (I assume should an input not match with anything on the index sheet it would return nothing thus confirming human error at that point?).
Regarding the code I have been working with, I cannot figure where to start. My experience with VBA only provides insight towards its potential; I cannot find a suitable example to even attempt trial and error to any real constructive progress. Find loops and Copy and Paste loops can work, but trying to return the data to a page that has formulas is where I run into issues. A link to a set of dummy data with deliberate redundancies (not to be removed) and deliberate blank cells is below; I hope that is enough data for a sufficient example.
http://ge.tt/96aKAAm2
I will review the example you sent in the meantime.
Thanks so much,
crlarus