Referencing and transposing entire rows into new worksheets

[sdr3rpa]

Hello all,
I've been trying to figure out this problem for some time. I have a workbook, with a worksheet that theoretically would look like this:

Capture.JPG

On the other worksheets, I would like each sheet to be for a particular division and list all the rows for that particular division. For example, there would be a sheet for 'Finance' that would automatically search for finance division cells and enter all the data for those rows

First row: 1, Bob, Finance;
Second Row: 3, Adam, Finance

This document would be a fluid document, where names can be removed and changed, and regardless of what names are entered, or how many, the code will still search for the entries listed with the correct division.

I first assumed I could use VLOOKUP for this, but because there will be repeat values I would not be able to use that since it would only look for the first value in the list and stop there. I found a tutorial that allows me to use the INDEX, SMALL, IF and ROW functions to find the value within a particular cell in the same range, but I'm not sure how to make it give me the whole row and not a corresponding cell within the same one. (https://fiveminutelessons.com/learn-...le-values-list).

I think I can even figure out how to get rid of null values, but I can't figure out what the basic code would be for pulling information. Any suggestions?

Thank you

[JohnTopley]

Attach a sample workbook (not image).

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.

[JohnTopley]

Guessing your file ..

Try

in your "Finance" Sheet with your "master" data in Sheet1

=IFERROR(INDEX(Sheet1!$A$2:$Z$1000,SMALL(IF(Sheet1!$C$2:$C$1000="Finance",ROW(Sheet1!$A$2:$Z$1000)-ROW($A$2)+1,""),ROWS($A$2:A2)),COLUMNS($A:A)),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Copy across and down


Copy same formula for other sheets, changing "Finance" to "Sales", "Admin" ....

[sdr3rpa]

John,
I uploaded a practice workbook, and I hope it works for what you requested.

I also tried the formula provided and using the "Finance" code I only pull out one entry for a Sales division.

I appreciate your help!

[JohnTopley]

See attached.

[sdr3rpa]

John, that worked perfect after changing it over to my application!

Thanks a million!