formula to find a number in a range then use the data in same column but row 16

[bdouglas1011]

I have a sheet that in a cell I want to search a range (O19:W27) and if it finds an exact match to the data in (F7) EX:"SDM-0011" ---data will have numbers and letters and will change. If it finds that exact match in the specified range it grabs the number in the same column above it on row 16.

Sometimes the same match will be found in the specified range more than once so I would like the number grouped together separated by comma.
Ex: if found in 2 spots and the number in row 16 are 2 & 3 then it would return "2,3" in cell (E11)

I use this formula on other parts of the sheet

=IF(F7="","",SUMPRODUCT(($O$19:$W$27=F7)*IF(ISNUMBER($O$7:$W$7),$O$7:$W$7)))

an array that adds the numbers together that match what it was looking for but instead of adding the data together I want it to display it as it is.

Right now it returns "5"
what I want is "2,3"

I know its doing it because of the "sumproduct" but can I use something else to give me the result I want

[Pepe Le Mokko]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[bdouglas1011]

data I am searching for is in "F7" = SDM-0011
Range to check "O19:W27"
when it finds a match ....return the value in ROW 7 above the match in the same column and place it in E11

Search for another match in the range and group the data separated by commas


See on the Before Page cell E11 is blank
See on After Page it has "2,3"

[Glenn Kennedy]

One way..

Please Login or Register  to view this content.

How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


Then an array formula in E11:

=ConcatAll(IF($O$19:$W$27=$F$7,$O$7:$W$7,""),", ")


Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[bdouglas1011]

I have a question if you don't mind. I had another post trying to get this same result an easier way but really have not had any response.

The 3 data sheets that I import into this sheet work OK this way because I export them out of 3 workbooks which changes them into an .XLSX
then I can just do three separate imports and place the data the way you see to get this result using these formulas.

What I would love is to be able and have some code that when run it would ask you what file you would like for sources (different workbooks) and then it would compile this same information and get the same result just easier and quicker.

The problem I ran into is the original workbooks are .XLSM and when anything starts to run it stops and nothing happens.

Then I thought maybe it could take the file and open it then convert the sheet into .XLSX in the code and then in turn use that info to give the same result.

I have code that within each workbook exports the file ok...but when I try to have a dialogue box ask you for your source file once it opens the code does not run.

Any thoughts...

Essentially I usually have about 3 different workbooks that I would love be able to either link to the master inventory to track hours or have code to run the 3 sheets on to get this data.

It is hard to explain do you think it could be done?

[Glenn Kennedy]

The wee bit of code that I posted is pretty much all that I can do with VBA. I use VBA like a drunk man uses a lampost... for support, not illumination.

[Glenn Kennedy]

I have just looked at your other thread.... I didn't understand a bit of it!!!!

[bdouglas1011]

Thanks now that i Have this working I might end that thread and try to re explain it better.

Thanks for your help