Lookup Multiple Pieces of Data...

[Alan]

I'm trying to find a solution to my problem and I'm pretty sure its
gonna have to be in VBA (hoping its a simple answer). The relevant
information is as follows:

I have 1 worksheet (called "Data") with 412 managers in column A and
30 funds in row 1 (each fund has its own worksheet in the workbook and
the data gets pulled into this summary worksheet called "Data"). If
one of the managers in Column A can be found in one of the Funds in
row 1, there is a "Yes" in the corresponding cell.


Fund 1 Fund 2 Fund 3 Fund 4
Fund 5 etc...
Manager 1 Yes Yes
Manager 2 Yes
Manager 3 Yes
Yes Yes
Manager 4 Yes Yes
Manager 5
etc...


I am trying to make a summary worksheet (called "Searchable Data")
that will have all the same Managers in Column A. However, instead of
having Yesses in cells B2, E2, etc, I want to have the Fund names in
Cells B2, C2, etc like below:


Manager 1 Fund 1 Fund 4
Manager 2 Fund 2
Manager 3 Fund 3 Fund 4 Fund 5
Manager 4 Fund 1 Fund 2
Manager 5
etc...

If I use the lookup formula in excel, it only picks up the first
reference. Trying to have it so that if the first reference was
picked up in Cell B2, then cell B3 picks up the next reference (the
next fund that has that corresponding manager).

Thanks in advance for all your help (I'm hoping its a simple answer).

 

[Dave Peterson]

You don't need a macro.

Copy the worksheet to a new name (don't break the original)

If you have other stuff in that table data area (B2:....), then make sure those
cells are empty (use a bunch of edit|replaces???)

Now select B2:thebottomrightcornercell
Edit|goto|special|Constants
and hit enter

Notice that just the cells that have Yes in them are selected.

One cell will be the activecell in that selection. I'm gonna guess it's in
column B, but change this formula to match that activecell's column.

Type this:
=b$1
and hit ctrl-enter to fill all the cells with the appropriate formula (excel
will adjust the formula for each column).

Now select that range again:
B2:thebottomrightcornercell

Edit|goto|special|blanks
And only the blank cells will be selected
Edit|delete|shift cells left

And clean up the headers in row 1.

 

[T. Valko]

This can be done with formulas but it might be slow based on the amount of
data you're searching/extracting.

Here's a sample file:

Alan_lookups.xls 23.5kb

http://cjoint.com/?dBvi2KGH0E

As you'll see, the formula(s) are pretty "healthy".

Biff

 

[Alan]

Biff - That was exactly what I needed. It is a little slow since
there is so much data (I have automatic calculations turned off
otherwise it would not be too much fun to work with) but it does what
I need.

Dave - Thanks for your response as well. I was looking for something
formula wise because the manager names in column A will probably be
changing every quarter due to funds getting in and out of managers.
Thus the formula will let me easily update the stuff quarterly.

Alan

 

[Alan]

Biff,

I do have a few questions for you on your formula:

=IF(COLUMNS($A:A)<=COUNTIF(INDEX($B$2:$F$10,MATCH($A13,$A$2:$A
$10,0),),"Yes"),
INDEX($B$1:$F$1,SMALL(IF(($A$2:$A$10=$A13)*($B$2:$F
$10="Yes"),COLUMN($B2:$F10)-MIN(COLUMN($B2:$F10))
+1),COLUMNS($A:A))),"")

In the Columns($A:A) portions, how come the second A is a relative
reference and not an absolute (I.e., when I drag it across, it becomes
$A:B, etc). (Same holds for the Column formulas... Why are the
columns absolute but the rows relative?).

Also in the latter part of the formula, I redid it like this and it
works:

COLUMN(Data!$B$2:$AG$439)-MIN(COLUMN($B$2:$AG$439))+1)

You'll notice that i put a link to the Data spreadsheet in the first
part of the formula but not in the "-Min(Column)" part of the
formula. It works the same if I include the link to Data in the
second part of the formula
COLUMN(Data!$B$2:$AG$439)-MIN(COLUMN(Data!$B$2:$AG$439))+1).

Any idea why it works both ways?

(Questions really for my edification).

 

[T. Valko]

Question 1:

We need the COLUMNS($A:A ) argument to increment.

In the first instance of COLUMNS( ) it's being used as a pseudo error trap.
It counts the number of columns the formula is copied to and compares that
result with the count of "yes". For example, if the count of "yes" is 3
then:

=IF(COLUMNS($A:A)<=COUNTIF(INDEX($B$2:$F$10,MATCH($A13,$A$2:$A
$10,0),),"Yes")

Evaluates to:

=IF(1<=3,............)

As you copy across this is what you get:

=IF(1<=3,............)
=IF(2<=3,............)
=IF(3<=3,............)
=IF(4<=3,............)

The second instance of COLUMNS($A:A) is being used to increment the second
argument of SMALL( ).

If the above comparison is TRUE: =IF(1<=3,............)

Then the formula calculates the column numbers where the "yes" appears.
These column numbers are passed to the SMALL function and then we use
COLUMNS($A:A) to tell SMALL which column number meets the condition. For
example:

......A.....B.....C.....D
............Yes.........Yes

SMALL{FALSE,2,FALSE,4},COLUMNS($A:A)

In the above, columns B and D meet the criteria. The column numbers are 2
and 4. The COLUMNS argument evaluates to 1 so this tells the SMALL function
we want the 1st smallest number from the array FALSE,2,FALSE,4. As you copy
the formula across the COLUMNS argument increments and then tells the SMALL
function that we want the 2nd smallest, 3rd smallest, etc.

Question 2:

The COLUMN function takes an argument of either form: A:A or A1:A1. Both of
those mean exactly the same thing, 1. The column refernce is the only
portion that matters. Using A1, the row reference is ignored. Using it like
this:


COLUMN(DATA!A11)

The sheet name is irrelavent and is also ignored.

These will evaluate exactly the same:

COLUMN($A:$D)
COLUMN(DATA!$A$1:$D$1)

So, it's just a preference to use only enough info in the formula as is
needed. (saves typing and shortens the formula).

One advantage to including that info in the formula is that it makes it
easier (maybe?) to follow the formula references. But I can do that without
that added info.

Biff

 

[Guest]

Alan,
Not sure how you get to your "Yes" but I suggest you look at replacing the
"Yes" with the Fund indicator if possible - that would remove 1 step out of
2. If it is formual based, that should be easy, ie rather then "Yes" refer to
$A2

If that is possible, a very basic macro can do:
Copy the whole table
start at manager 1
check the cells on the right one by one, if empy, delete/move right.
To create the macro, just record the first two steps & delete one cell. Then
change the recorded macro to check for an empty cell:

MyLoop:
for iCounter = 1 to 30
activecell.offset(0,1).select
if activecell = empty then
Selection.Delete Shift:=xlToLeft
end if
next
activecell.offset(1,0).select
if activecell <> empty goto MyLoop
exit sub

Another option is to transpose the whole table, sort each column and
transpose back.

 

[Guest]

TRY:


Sub Summarise()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim irow As Long, orow As Long
Dim Lastrow As Long, irow As Long
Dim icol As Integer, jcol As Integer

Set ws1 = Worksheets("Data")
Set ws2 = Worksheets("Searchable Data")

col = 1 '<=== column for lastrow calculation

With ws1

Lastrow = .Cells(Rows.Count, col).End(xlUp).Row
.Cells(1, 1).Resize(Lastrow, 1).Copy ws2.Cells(1, 1)

For irow = 2 To Lastrow
jcol = 1
For icol = 2 To 31
If .Cells(irow, icol) = "Yes" Then
jcol = jcol + 1
ws2.Cells(irow, jcol) = .Cells(1, icol)
End If
Next icol
Next irow

End With


End Sub

HTH

 

[Lori]

One other method using only menu commands:

1. Edit > Replace "Yes" with "=1:1"
2. Edit > Copy / Paste Special Values
3. Edit > Goto > Special > Blanks
4. Edit > Delete > Shift Cells Left

Result...

Man1 Fund1
Man2 Fund2 Fund3
Man3 Fund5
Man4
Man5 Fund4
Man6 Fund1 Fund2
Man7
Man8
Man9 Fund3