# Unique list from column based on column and output in row

#### ruchperformive

##### New Member
Hello,

I'm trying to populate all the unique (i.e billing cycle) related to my invoices

Given

 Invoice id Value billingcycle date_sent_month_year date_paid_month_year due_date_month_year 1​ 10​ One Time 4/30/2021​ 4/30/2021​ 4/30/2021​ 1​ 20​ One Time 4/30/2021​ 4/30/2021​ 4/30/2021​ 1​ 10​ Monthly 4/30/2021​ 00-00-0000 5/14/2021​

Desired Result

 Invoice Amount Due_Date Billing_Cycle 1 40​ 4/30/2021​ Monthly one time 2 2​ 9/15/2019​ Monthly Annually 3 10​ 9/16/2019​ Monthly 4 4​ 9/16/2019​ Monthly 2 Year

How can I check all unique value related to invoice from 2 separate sheet

Currently I'm using

=IFERROR(INDEX("Sheet 1 Billing Cycle", MATCH(0, IF("Invoice"="Sheet 1 Invocies", COUNTIF("Invoice", "Sheet 1 Invoices), ""), 0)),"")

But this is only working for Column and wont give me output in ROW and also wont consider another sheet 2 data.

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### kweaver

##### Well-known Member
It's unclear to me where you're getting the info for the "desired result"...did you only show part of the data that contributed to it?
It would be better if you posted more info using XL2BB so that recreating it doesn't require retyping it.

#### ruchperformive

##### New Member
Here with the xl2bb

I'm trying to populate all the unique (i.e billing cycle) related to my invoices

Given
Book2((Unsaved-308857102210271637)).xlsx
ABCDEFGHIJKLMNOP
1WHMCS clientidcompanynameInvoice id Value idplan_idcredittotal_amountstatuspaymentmethoddescriptionbillingcycledate_sent_month_yeardate_paid_month_yeardue_date_month_yearDue date
2177257579120.7NULLNULL020.7PaidquantumgatewayOne Time4/30/20214/30/20214/30/20214/30/2021
317435757909.3NULLNULL09.3PaidquantumgatewayOne Time4/30/20214/30/20214/30/20214/30/2021
41976257578727525486602UnpaidquantumgatewayMonthly4/30/202100-00-00005/14/20215/14/2021
51059357578633185729603UnpaidquantumgatewayMonthly4/30/202100-00-00005/14/20215/14/2021
61747257578537962987103.21UnpaidquantumgatewayMonthly4/30/202100-00-00005/14/20215/14/2021
71876557578438028786403UnpaidquantumgatewayMonthly4/30/202100-00-00005/14/20215/14/2021
82819957578339347078803UnpaidquantumgatewayMonthly4/30/202100-00-00005/14/20215/14/2021
92329757578119.9574704870019.95UnpaidpaypalbillingMonthly4/30/202100-00-00005/14/20215/14/2021
101690457577721.9566512855021.95UnpaidquantumgatewayMonthly4/30/202100-00-00005/14/20215/14/2021
1117125757766.956319729306.95UnpaidquantumgatewayMonthly4/30/202100-00-00005/14/20215/14/2021
12270425757759.957984086609.95UnpaidpaypalbillingMonthly4/30/202100-00-00005/14/20215/14/2021
1358365757615016099306050UnpaidquantumgatewayMonthly4/30/202100-00-00005/14/20215/14/2021
1415730575760185950307410370UnpaidpaypalbillingMonthly4/30/202100-00-00005/14/20215/14/2021
1515730575760185950317410370UnpaidpaypalbillingMonthly4/30/202100-00-00005/14/20215/14/2021
168510575759350888077440704UnpaidquantumgatewayOne Time4/30/202100-00-00005/14/20215/14/2021
17851057575948.5888109960704Unpaidquantumgateway1 Year(s)4/30/202100-00-00005/14/20215/14/2021
188510575759199888083570704UnpaidquantumgatewayMonthly4/30/202100-00-00005/14/20215/14/2021
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C1Cell ValueduplicatestextNO
C2:C18Cell ValueduplicatestextNO

My desired Result is

Book2((Unsaved-308857102210271637)).xlsx
ABCDEFGHIJKLMNO
257579120.7\$ -1200\$ -\$ 12.00\$ -########1/0/1900One Time
35757909.3\$ -010\$ -\$ 1.00\$ -\$ -1/0/1900One Time
45757872\$ -020\$ -\$ 2.00\$ -\$ -1/0/1900Monthly
55757863\$ -1\$ 1.00\$ -\$ -9/15/2019Monthly
65757853\$ -\$ 3.00\$ 3.00\$ -\$ -9/15/2019Monthly
75757843\$ -\$ 1.00\$ 1.00\$ -\$ -9/16/2019Monthly
85757833\$ -1\$ 1.00\$ 35.80\$ -9/16/2019Monthly
957578119.95\$ -10\$ 10.00\$ -\$ -9/16/2019Monthly
1057577721.95\$ -123\$ 6.00\$ -\$ -9/17/2019Monthly
115757766.95\$ -0.95\$ 0.95\$ -\$ -9/17/2019Monthly
125757759.95\$ -0.95\$ 0.95\$ -\$ -9/17/2019Monthly
1357576150\$ -5\$ 5.00\$ -\$ -9/18/2019Monthly
14575760370\$ -0190.80\$ -\$ 190.80\$ -\$ -9/18/2019MonthlyOne Time
155757591074\$ 1,074.00\$ 1,074.009/18/2019MonthlyOne Time1 Year(s)
Sheet4
Cell Formulas
RangeFormula
L2:L15L2=IF(MAXIFS('1.Ruchit_Monthly_Data'!P:P,'1.Ruchit_Monthly_Data'!C:C,'5. Credit Classification'!A2)>0,MAXIFS('1.Ruchit_Monthly_Data'!P:P,'1.Ruchit_Monthly_Data'!C:C,'5. Credit Classification'!A2),MAXIFS('2.Ruchit_NM_Data'!O:O,'2.Ruchit_NM_Data'!C:C,'5. Credit Classification'!A2))
M2M2=IFERROR(INDEX('6.Consolidated_Revenue'!\$D\$2:\$D\$230466, MATCH(0, IF(\$A2='6.Consolidated_Revenue'!\$B\$2:\$B\$230466, COUNTIF(\$A2, '6.Consolidated_Revenue'!\$D\$2:\$D\$230466), ""), 0)),"")
M3:M15M3=IFERROR(INDEX('6.Consolidated_Revenue'!\$D\$2:\$D\$230466, MATCH(0, IF(\$A3='6.Consolidated_Revenue'!\$B\$2:\$B\$230466, COUNTIF(\$A3, '6.Consolidated_Revenue'!\$D\$2:\$D\$230466), ""), 0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
'5. Credit Classification'!_FilterDatabase='5. Credit Classification'!\$A\$4:\$L\$2331L4
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A15Cell ValueduplicatestextNO
A2:A14Cell ValueduplicatestextNO
A1,A16Cell ValueduplicatestextNO
J7:K7Cell ValueduplicatestextNO
A1,A16Cell ValueduplicatestextNO

Problem

Current Formula

{=IFERROR(INDEX('6.Consolidated_Revenue'!\$D\$2:\$D\$230466, MATCH(0, IF(\$A3='6.Consolidated_Revenue'!\$B\$2:\$B\$230466, COUNTIF(\$A3, '6.Consolidated_Revenue'!\$D\$2:\$D\$230466), ""), 0)),"")}

this formula can pull all unique billing cycle list vertically but those output to be executed horizontally.

Replies
8
Views
118
Replies
5
Views
85
Replies
1
Views
42
Replies
2
Views
122
Replies
1
Views
74

1,141,129
Messages
5,704,448
Members
421,350
Latest member
jake9951

### 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