Lost, Really Lost. Please Help with VLookup

[clbryan26]

I am working with a Non-Profit as their IT Coordinator. They are using mostly paper based case management at this point. I have got them to start using Salesforce. Hard enough in itself. They have residential treatment (there are clients on property). They use word to make room/door lists weekly. Right now, that is a matter of manual entry in Word.

So, I tried doing an export in Salesforce, I have Several Columns (Room Number, Bed Number, and Resident Name).

I tried to use Vlookup, I tried Advance Filtering. I have only a limited college stats level of Excel Knowledge.

I would like to get Each Room into it's own sheet so I can print Room Lists.

Room Number	Bed Number	Resident Name	Case Manager	Other Colums
101	1B	Joe Smith	Bob Jones	Blah
101	1T	Jim Campbell	Chris Smith	Blah
101	2B	John Doe	James Campbell	Blah
102	1T	Chris Weaver	Bob Jones	Blah
102	1B	Eric Brown	Chris Smith	Blah

<tbody>
</tbody>

That's sort of how the table is set up from Salesforce as an export. I would like to have a worksheet with each room number in it (I have already created worksheets for each of them) but I can't seem to get any of the forumulas or filters to update or paste the right info.

Please help!

Thanks,

 

[Lee.]

vlookup will only reference the first match.

To put them into different sheets, you could use a PivotTable then do drilldown for each room number which would create a new worksheet.

 

[clbryan26]

I went and made worksheets. I labeled each one with like 101, 102, etc... I just need to get the right people on each sheet. I tried the vlookup, and like you said it only returns the first match. I tried to use advanced filters (but each time i change the data I have to redo the filter). I tried to look into INDEX, and stuff but I got lost. Really lost. I will try the pivot table, but will that do it dynamically or will I have to redo the table every time I add or remove data.

 

[Lee.]

Maybe something like

Sub test()
Dim x as String
    For x = 101 To 150
    Columns("A:E").Select
    Selection.AutoFilter
    ActiveSheet.Range("A:E").AutoFilter Field:=1, Criteria1:=x
    Columns("A:E").Copy
    Sheets(x).Select
    Range("A1").PasteSpecial
    Sheets("Export").Select
    Columns("A:E").Select
    Selection.AutoFilter
    Next x
End Sub

 

[Aladin Akyurek]

Create the relevant sheets, that is, 101 and 102.

Activate 101.

In A1 enter:

=REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"")

Let Sheet1, A:E, house the room data you posted.

A3, control+shift+enter, not just enter, and copy down:

=IFERROR(SMALL(IF(Sheet1!$A$2:$A$6=$A$1,
  ROW(Sheet1!$A$2:$A$6)-ROW(Sheet1!$A$2)+1),ROWS($A$3:A3)),"")

B2, just enter, copy across as far as needed, then copy down:

=IF($A3="","",INDEX(Sheet1!A$2:A$6,$A3))

 

[clbryan26]

Awesome guys, thanks! So far this is working. I will post again in a few hours.

Thanks, Thanks, Thanks!!!

 

[Aladin Akyurek]

Awesome guys, thanks! So far this is working. I will post again in a few hours.

Thanks, Thanks, Thanks!!!

You are welcome.