Re: INDEX/MATCH Function with Non-Contiguous Array
This seems a bit complicated, maybe an example showing how you come up with the result will help. Also you might consider starting a new thread with an appropriate title.
Best Regards
Re: INDEX/MATCH Function with Non-Contiguous Array
This seems a bit complicated, maybe an example showing how you come up with the result will help. Also you might consider starting a new thread with an appropriate title.
Best Regards
Re: Return multiple text rows based on search value
I cannot work on it right now, give me two options and I will get back to you. Colon (:) might not work 100% the other option in a separate column is more solid. I will get back ASAP
Best Regards
Re: Return multiple text rows based on search value
This will not work. In the sample you provided the categories have a common characterstic, Category A, Category B...
in your actual data how do you distinguish the header from other items?
Re: Return multiple text rows based on search value
Just D2,D3 will populate from the macro
Re: Return multiple text rows based on search value
Hi Mitch,
How it works:
1. In Sheet2 (the report sheet) in cell D2 you select if you want to report by category or location.
2. Depending on your selection cell D3 will show all available categories or locations from the data sheet.
3. Once you make your selection in cell D3 the macro will retrieve the relevant info from the data sheet.
How to customize to your actual workbook:
1. Do all the testing on a backup file.
2. Open the example wbk, press Alt+F11, it will open the VB editor.
3. On the left pane, double-click on Sheet2 and you will see the macro on the right pane. Select all lines and copy.
4. I've written some comments that will help you to adjust worksheets and ranges to your actual data.
5. Open your backup copy of the actual wbk, press Alt+F11.
6. On the left pane, double-click on the sheet where you want the report to be, you will get a blank page on the right pane.
7. Paste the macro from the example wbk to the backup wbk.
8. In the backup wbk VB editor, go to file menu Insert and select Module, you will see a blank page on the right pane.
9. Go to the example wbk VB editor, double-click on module1 and copy the macro into the backup wbk in module1.
10. Adjust the sheet names and ranges to your actual data and test the results.
Hope that helps.
Best Regards
Re: INDEX/MATCH Function with Non-Contiguous Array
Check now, I think is OK it can handle the trigger value of 0.
However but bear in mind that MATCH returns the first value, so if A and B have the same min value and both are 1 it will return A, I don't know if this is an issue. If it is, then how you handle this situation?
Best Regards
Re: Return multiple text rows based on search value
It can, I can combine them into one and post back, just give me some time please. Before I proceed, this will make things easier for you, where you want the report to be in the same sheet or in another one?
Re: INDEX/MATCH Function with Non-Contiguous Array
I apologize! I didn't see that you were browsing this thread and posted my solution, otherwise I would leave for you to answer (1,300 posts...........!!!!)
Re: INDEX/MATCH Function with Non-Contiguous Array
Although I received an email notification that rory has contributed to this thread, and powera86 answer implies this I cannot see rory's contribution.
Re: Compare Range from different workbooks and if range match copy and paste.
Hi Jacwe,
Welcome to Ozgrid. The ranges are not static, but the structure of the two workbooks are static? Book1 range will always start from Sheet1, B2:D? Book2 range will always start from Sheet1, A1:C?
Also, any chance for duplicate code, i.e. [TABLE="width: 131"]
[tr]
[TD="class: xl64, width: 131"]4527388B/4494292B appearing twice or more in the list?[/TD]
[/TABLE]
Best Regards
Re: INDEX/MATCH Function with Non-Contiguous Array
Hi,
Check the attached, one array formula for the min (colB) and a normal index/match for colA.
Best Regards
Re: Return multiple text rows based on search value
Check the attached.
Best Regards
Re: Return multiple text rows based on search value
Hi Mitch,
Welcome to Ozgrid. Can you please upload a small workbook with dummy data but with the actual structure and the desired result?
Re: INDEX/MATCH Function with Non-Contiguous Array
Can you give a scenario on how you would like this to work?
Re: Exclude Column From MIN(IF array formula
Hi RDF,
I don't think you can use non-contiguous range in an array formula. Try this array formula = MIN(MIN(IF(ACD_IN!$E$4:$AE$4=AF$4,ACD_IN!$E$9:$AE$9)),MIN(IF(ACD_IN!$AG$4:$AJ$4=AF$4,ACD_IN!$AG$9:$AJ$9)))
Best Regards
Re: Extracting the first numbers from alphanumeric strings
I relied on this
Quote
I believe it's because the format of my strings are number_text_number_text_number_text
So it can be text_number_text..... and then return the first set of numbers?
Re: Extracting the first numbers from alphanumeric strings
Hi Johan,
Welcome to Ozgrid. Copy the below into a standard module, then in your worksheet type this formula = NumberSet( your cell containing the string).
Function NumberSet(InputSet As String) As Long
Dim ThisValue As Variant
Dim Index As Long
For Index = 1 To Len(InputSet)
ThisValue = Mid(InputSet, Index, 1)
If IsNumeric(ThisValue) Then
NumberSet = NumberSet & ThisValue
Else
Exit Function
End If
Next Index
End Function
Display More
Best Regards
Re: Index Match issues
Hi Donna,
I'm glad that it worked for you. Thanks for the feedback.
Best Regards
Re: Index Match issues
Hi Donna,
Regarding the site column you must first select the range of cells that represent the total number of the sites required. For example in the test worksheet 10 cells are needed. Then with all cells selected you write the formula in the first cell (just start typing) and when done instead of enter press control-shift-enter. Now in the test workbook because the report area starts from row 6 I wrote -5 meaning start from 1. I use the small function. The small function returns the nth smallest number in an array. So row() equals 6 -5 it will return the 1st smallest number in the array. Hope that makes sense now.
Best Regards
Re: Index Match issues
Hi Donna,
I figured out another solution, only formulas no macros, but to keep it simple you need to set up three "help" columns. See the attached and if you are happy I can explain how to adjust the formula ranges to your actual workbook ranges.
Best Regards