Unique list from column based on column and output in row

[ruchperformive]

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.

 

[kweaver]

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]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	WHMCS clientid	companyname	Invoice id	Value	id	plan_id	credit	total_amount	status	paymentmethod	description	billingcycle	date_sent_month_year	date_paid_month_year	due_date_month_year	Due date
2	1772		575791	20.7	NULL	NULL	0	20.7	Paid	quantumgateway		One Time	4/30/2021	4/30/2021	4/30/2021	4/30/2021
3	1743		575790	9.3	NULL	NULL	0	9.3	Paid	quantumgateway		One Time	4/30/2021	4/30/2021	4/30/2021	4/30/2021
4	19762		575787	2	75254	866	0	2	Unpaid	quantumgateway		Monthly	4/30/2021	00-00-0000	5/14/2021	5/14/2021
5	10593		575786	3	31857	296	0	3	Unpaid	quantumgateway		Monthly	4/30/2021	00-00-0000	5/14/2021	5/14/2021
6	17472		575785	3	79629	871	0	3.21	Unpaid	quantumgateway		Monthly	4/30/2021	00-00-0000	5/14/2021	5/14/2021
7	18765		575784	3	80287	864	0	3	Unpaid	quantumgateway		Monthly	4/30/2021	00-00-0000	5/14/2021	5/14/2021
8	28199		575783	3	93470	788	0	3	Unpaid	quantumgateway		Monthly	4/30/2021	00-00-0000	5/14/2021	5/14/2021
9	23297		575781	19.95	74704	870	0	19.95	Unpaid	paypalbilling		Monthly	4/30/2021	00-00-0000	5/14/2021	5/14/2021
10	16904		575777	21.95	66512	855	0	21.95	Unpaid	quantumgateway		Monthly	4/30/2021	00-00-0000	5/14/2021	5/14/2021
11	1712		575776	6.95	63197	293	0	6.95	Unpaid	quantumgateway		Monthly	4/30/2021	00-00-0000	5/14/2021	5/14/2021
12	27042		575775	9.95	79840	866	0	9.95	Unpaid	paypalbilling		Monthly	4/30/2021	00-00-0000	5/14/2021	5/14/2021
13	5836		575761	50	16099	306	0	50	Unpaid	quantumgateway		Monthly	4/30/2021	00-00-0000	5/14/2021	5/14/2021
14	15730		575760	185	95030	741	0	370	Unpaid	paypalbilling		Monthly	4/30/2021	00-00-0000	5/14/2021	5/14/2021
15	15730		575760	185	95031	741	0	370	Unpaid	paypalbilling		Monthly	4/30/2021	00-00-0000	5/14/2021	5/14/2021
16	8510		575759	350	88807	744	0	704	Unpaid	quantumgateway		One Time	4/30/2021	00-00-0000	5/14/2021	5/14/2021
17	8510		575759	48.5	88810	996	0	704	Unpaid	quantumgateway		1 Year(s)	4/30/2021	00-00-0000	5/14/2021	5/14/2021
18	8510		575759	199	88808	357	0	704	Unpaid	quantumgateway		Monthly	4/30/2021	00-00-0000	5/14/2021	5/14/2021
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1	Cell Value	duplicates	text	NO
C2:C18	Cell Value	duplicates	text	NO

My desired Result is

Book2((Unsaved-308857102210271637)).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Invoice	Invoice Amount	Erroneous_Credit	Actual_Credit	Bad_Debt	Bank Fees	Payement	Total Amount Distribution	Tax	Match		Due_Date	Billing_Cycle
2	575791	20.7	$ -	12	0	0	$ -	$ 12.00	$ -	########		1/0/1900	One Time
3	575790	9.3	$ -	0	1	0	$ -	$ 1.00	$ -	$ -		1/0/1900	One Time
4	575787	2	$ -	0	2	0	$ -	$ 2.00	$ -	$ -		1/0/1900	Monthly
5	575786	3	$ -	1				$ 1.00	$ -	$ -		9/15/2019	Monthly
6	575785	3	$ -				$ 3.00	$ 3.00	$ -	$ -		9/15/2019	Monthly
7	575784	3	$ -				$ 1.00	$ 1.00	$ -	$ -		9/16/2019	Monthly
8	575783	3	$ -			1		$ 1.00	$ 35.80	$ -		9/16/2019	Monthly
9	575781	19.95	$ -	10				$ 10.00	$ -	$ -		9/16/2019	Monthly
10	575777	21.95	$ -	1	2	3		$ 6.00	$ -	$ -		9/17/2019	Monthly
11	575776	6.95	$ -	0.95				$ 0.95	$ -	$ -		9/17/2019	Monthly
12	575775	9.95	$ -	0.95				$ 0.95	$ -	$ -		9/17/2019	Monthly
13	575761	50	$ -			5		$ 5.00	$ -	$ -		9/18/2019	Monthly
14	575760	370	$ -	0	190.8	0	$ -	$ 190.80	$ -	$ -		9/18/2019	Monthly	One Time
15	575759	1074					$ 1,074.00	$ 1,074.00				9/18/2019	Monthly	One Time	1 Year(s)
Sheet4

Cell Formulas
Range	Formula
L2:L15	L2	=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))
M2	M2	=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:M15	M3	=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
Name	Refers To	Cells
'5. Credit Classification'!_FilterDatabase	='5. Credit Classification'!$A$4:$L$2331	L4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A15	Cell Value	duplicates	text	NO
A2:A14	Cell Value	duplicates	text	NO
A1,A16	Cell Value	duplicates	text	NO
J7:K7	Cell Value	duplicates	text	NO
A1,A16	Cell Value	duplicates	text	NO

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.