Unique list from column based on column and output in row

ruchperformive

New Member
Joined
Mar 2, 2021
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello,

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

Given

Invoice idValuebillingcycledate_sent_month_yeardate_paid_month_yeardue_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

InvoiceAmountDue_DateBilling_Cycle
1
40​
4/30/2021​
Monthlyone time
2
2​
9/15/2019​
MonthlyAnnually
3
10​
9/16/2019​
Monthly
4
4​
9/16/2019​
Monthly2 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
Joined
May 8, 2018
Messages
2,105
Office Version
  1. 365
  2. 2010
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
Joined
Mar 2, 2021
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
1InvoiceInvoice AmountErroneous_CreditActual_CreditBad_DebtBank FeesPayementTotal Amount DistributionTaxMatchDue_DateBilling_Cycle
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.
 

Forum statistics

Threads
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.
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