Using VBA / INDEX / VLOOKUP to return a whole row on a different spreadsheet

[thedicewoman]

Hello there,

I've trawled the internet and can't seem to get just the right answer for my query: I hope someone here can help.

I have built a report with a front page, a calculations page, a clean data page and an export data page. The Clean Data page picks up what the user exports into the export data page and runs some neat little formulas to remove any erroneous records. It has all of the raw data I need to build the Front Page.

On the Front Page, I'd like to include some tables which will read the Clean Data page and return records (whole rows, around 6 columns) dependant on certain parameters.

So, lets say I want all records (rows) displayed on the Front Page which have 'UK' included in the 'Project Location' column on the Clean Data page. Is there a formula I can use to return:
a) more than one record
b) the entire row of that record?

The aim is for any user to simply enter the Export Data and have a quickly-produced front page report with certain parameneters set to return highlighted records.

Any advice you can give on this would be most appreciated: I am not shy in using VBA for a solution, as long as I can build in a user-friendly 'Generate Report' button. I have 2007.

Many thanks

 

[circledchicken]

Hi and welcome to the forum,

Here is one possible solution you can try with this example setup:

Excel 2013

	A	B	C	D
Project Location:	UK
Count:
Column index
Row index	Project Location	Project Type	Project Number
UK	A
UK	B
UK	E

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]5[/TD]

</tbody>

Sheet1​

Excel 2013

	A	B	C
Project Location	Project Type	Project Number
UK	A
UK	B
France	C
Germany	D
UK	E

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]4[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]5[/TD]

</tbody>

Sheet2​

The associated formulas for Sheet1 are as follows.

In B2 enter:

=COUNTIF(Sheet2!A$2:A$6, B1)

In A6 (using CTRL-SHIFT-ENTER rather than just ENTER as this is an array formula) and drag down as far as required:

=IF(
    ROWS(A$5:A5) > B$2,
    "",
    SMALL(
      IF(
        Sheet2!A$2:A$6 = B$1,
        ROW(Sheet2!A$2:A$6) - ROW(Sheet2!A$2) + 1),
      ROWS(A$5:A5)))

In B6 enter and drag across and down as far as required:

=IF($A6 = "", "", INDEX(Sheet2!$A$2:$C$6, $A6, B$4))

Note that an easier approach might just be to use a PivotTable or filters on the original data assuming your users would be comfortable with that.

If you need additional criteria in Sheet1, e.g. like this:

Excel 2013

	A	B	C	D
Project Location:	UK
Project Type:	A
Count:
Column index
Row index	Project Location	Project Type	Project Number
UK	A
UK	A

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]2[/TD]

</tbody>

Sheet1​

Then you can use the following in B3 and A7 (and down).

In B3 enter:

=COUNTIFS(
    Sheet2!A$2:A$6, B1,
    Sheet2!B$2:B$6, B2)

In A7 (using CTRL-SHIFT-ENTER rather than just ENTER as this is an array formula) and drag down as far as required:

=IF(
    ROWS(A$6:A7) > B$3,
    "",
    SMALL(
      IF(Sheet2!A$2:A$6 = B$1,
      IF(Sheet2!B$2:B$6 = B$2,
        ROW(Sheet2!A$2:A$6) - ROW(Sheet2!A$2) + 1)),
      ROWS(A$6:A7)))

If you want further criteria continue the same pattern adding more conditions as required.

 

[thedicewoman]

HI there,

Thanks very much for your swift reply. I will give this a go: on first glance it seems it will do the trick!

Cheers

 

[circledchicken]

You're welcome - thanks for the feedback.