# Copying Data from another sheet based on if statement without blanks (hopefully alphabetized)

#### jaydog

##### New Member
Hello All,

GOAL:
Sheet 1 is the master spreadsheet (with more data than my example, but I am making it simple). Sheet 2 displays a summary of data. It will have two columns PAID FEE 1 & PAID FEE 2 with the list of business names that have paid the fee (hopefully alphabetized).

Sheet 1
 Business Name PAID FEE 1 PAID FEE 2 A B PAID C PAID D E PAID F G H PAID PAID I

<tbody>
</tbody>

Sheet 2
Desired Output
Column 1
 PAID FEE 1 PAID FEE 2 B C E H H

<tbody>
</tbody>

It would be nice if the business names were alphabetized under each column. The spreadsheet gets updated all the time so it should have a trigger that if sheet 1 changes to update sheet 2.

One option is on sheet 2:
=IF(PAID FEE 1 on B2= PAID, BUSINESS NAME on A2,“"), copy this equation down to the number of businesses.

One option is on sheet 2:
=IF(PAID FEE 2 on C2= PAID, BUSINESS NAME A2,“"), copy this equation down to the number of businesses.

I am breaking syntax but wanted to communicate the logic.
This works but there is blanks for the false statement. Also this doesn't run each time the sheet runs, correct? I also wanted it to alphabetize. I can do it with a filter but that doesn't seem like the proper way.

Do I have to create a macro some how? How would it look? The overall goal is to have a separate sheet that summarizes data from sheet 1 for someone that isn't tech savvy. So sheet 2 doesn't perform any calculations or make changes to sheet1 but displays the data in a summary.

I hope that makes sense, and I appreciate any help I can get.

Thanks Mr. Excel Community

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### Mike LH

##### Well-known Member
Hi,

I'm afraid this doesn't do alphabetical order but if you sort your original columns the formula isn't affected. Put this in a cell and drag right and then down until it starts returning blank cells.

=IFERROR(INDEX(Sheet1!\$A\$1:\$A\$100,SMALL(IF(Sheet1!B\$1:B\$100="Paid",ROW(\$B\$1:\$B\$100)),ROW(1:1))),"")

This is an array formula which must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.

Replies
1
Views
322
Replies
14
Views
792
Replies
3
Views
848
Replies
1
Views
135
Replies
7
Views
353

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,559
Messages
5,832,498
Members
430,136
Latest member
Asir Jefferson

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back