Return multiple values to single cell from array

[olliecottam]

Hi

First post here... I'm wondering if someone can help.

I'm trying to create a VBA code/formula that allows me to search through data on a spreadsheet and list the results within one cell. Through trawling numerous Excel forums I've so far managed to find VBA code and a user defined formula that does what I want, the only thing is I want the formula to search within an array, rather than a range. I'm afraid altering the VBA is beyond my skills!

I have a series of data - 1000 items reflecting information about 10 properties, with some of the items given a condition rating of A-D. I want to be able to concatenate information about all of the items rated D for building 01 in a single cell. At the moment, the following only gives me all of the condition D rated items for the 10 properties.

User defined formula:

=MYVLOOKUP(lookupvalue,lookup range,index col)

VBA Code:

Please Login or Register  to view this content.

My thoughts are that the Lookup Range could be an array as per the following, but the code needs to be amended to make it work:

IF($C$2:$C$1001=$G$6,$R$2:$R$1001,"")

Please can you help? I'd be really grateful.

[Kenneth Hobson]

Welcome to the forum!

I would probably use FindAll() for multiple range finds. http://www.cpearson.com/Excel/FindAll.aspx

Application.Filter() can be used on single dimension arrays. I am not sure that that would help in this case though.

I suspect that you might be better off doing a loop using lbound() and ubound() rather than For Each. If "i" was the counter, when a(i) for example = "D" then you know that b(i) equals your match in the other array for example. Use another counter like "j" and Redim Preserve c(1 to j) to fill your matches to return. Use Join() to return the c() as a vbLF delimited string.

If no other replies and you get stuck, post back and I will work something up when I get time maybe tonight.

[olliecottam]

Hi Kenneth

Thank you for your reply. I'm afraid you've totally lost me... I put the basic in VBA unfortunately! If you have time to work something up for me I'd be really grateful...

[olliecottam]

Hi Kenneth

Thank you for your reply. I'm afraid you've totally lost me... I put the basic in VBA unfortunately! If you have time to work something up for me I'd be really grateful...

[jindon]

My thoughts are that the Lookup Range could be an array as per the following, but the code needs to be amended to make it work:

IF($C$2:$C$1001=$G$6,$R$2:$R$1001,"")

Une in cell like

=MyLookUp(C2:C17,G6,R2:R17,CHAR(10))

Please Login or Register  to view this content.

[olliecottam]

Thank you - would this work instead of my formula/code, or alongside it?

[jindon]

If I understand correctly, it should work alone.

[olliecottam]

I'm either not using it write, or it doesn't do what I'm after.

Basically I need the formula to:
1. search the dataset for one value, then
2. within the results search for another value in another column, then
3. concatenate the contents of a third column into a single cell.

My formula/code does items 2 and 3, I just need a way of filtering the data it searches.

Thanks!

[jindon]

Then my guess was wrong.

I thought return multiple values from one column, not multiple columns...

[Kenneth Hobson]

It would be best to post a simple xlsx file so that we can see what you need. Use maybe 6 rows in the 2 or 3 columns and manually show in a cell what a search for something would return.

To attach a file: click the Go Advanced button in lower right of a reply, scroll down and click Manager Attachments, and then browse and upload the file.

[olliecottam]

Here you go:
- Formula and results need to be in column O of Building Summary Data tab
- The building number needs to be taken from column A of Building Summary Data tab and search within column C of the Schedule tab
- It then needs to search for "D" in column H of the Schedule tab
- then concatenate the contents of column T of the Schedule tab

Does that make sense?

Thank you

[olliecottam]

Hi Kenneth

Did you by any chance get to look at my problem?

Thanks

[Kenneth Hobson]

Did you want a static or dynamic solution? Static means you run a macro to update column O and add any new entries. This might be more attractive depending on the data. Dynamic could be via a UDF.

[olliecottam]

Thanks so much for replying.

There could be around 10,000 lines of data eventually, so I suspect a macro would be better as I can run it whenever required rather than have it running all the time and potentially crippling Excel. Do you agree?

[Kenneth Hobson]

Seems like I've seen your file in this forum or another.

Yes, I would go with a static run as needed. Always test on a backup copy.

In a Module, run the static Sub. Be sure to insert Chip Pearson's FindAll BAS file. The last Sub is a one off test.

Please Login or Register  to view this content.

[olliecottam]

Kenneth

Thank you so much, this is perfect.

Ollie

[olliecottam]

Kenneth

Thank you so much, this is perfect.

Ollie

[olliecottam]

Kenneth

Thank you so much, this is perfect.

Ollie