CODEWITHSUNDEEP
vbaexcelmatrixexcel-formula
BenCes
BenCes

Reputation: 165

Excel Matrix get (multiple) values

As you can see we've got a matrix with data in it. (I removed the header of rows and columns, since it's corporate information that isn't supposed to be spread around.)

Link: http://i49.tinypic.com/5jwayg.jpg

This is what I need:

I hope somebody is able to achieve this with just functions?

Upvotes: 0

Views: 758

Answers (2)

Scott Holtzman
Scott Holtzman

Reputation: 27249

VBA Solution.

If I understand your issue correctly, this should work, or at least be a great start.

Option Explicit

Function findvalues(rng As Range) As String

Dim cel As Range, str As String

For Each cel In rng
    If cel.Value = "X" Then str = str & "," & cel.Value
Next

findvalues = Mid(str, 2)

End Function

Implement it like so, given your spreadsheet in the link

In cell A3 =findvalues(B3:W3) In cell B2 =findvalues(B3:B48)

Upvotes: 1

jrad
jrad

Reputation: 3190

I'm not sure how you would get the value of both B11 and M11, but you can do this cell by cell. Just do this:

=IF(A1 = "X", COLUMN(A1), "")

That will get you the column number of A1 aka '1'. There's bound to be a way to get what you desire with VBA, but I'm not very experienced with that just yet and so I would be of little assistance to you.

Upvotes: 0

Related Questions