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

jaydog

New Member
Joined
Jun 17, 2015
Messages
1
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 NamePAID FEE 1PAID FEE 2
A
BPAID
CPAID
D
EPAID
F
G
HPAIDPAID
I

<tbody>
</tbody>

Sheet 2
Desired Output
Column 1
PAID FEE 1PAID FEE 2
BC
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 :)
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Mike LH

Well-known Member
Joined
Mar 17, 2015
Messages
566
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,964
Messages
5,599,069
Members
414,281
Latest member
Engjamal2021

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top