GoogleSheets: Result based on unique and countif formula

Administrative Note:

Is your forum profile showing the version of Excel that you need this to work for?

Members will tailor the solutions they offer to the version of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent versions of Excel are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the release number in your profile (e.g. MS365 Version 2211). This is in the About Excel section further down the Account page.

Thanks.

Thread moved to the correct section - please add GoogleSheets to your forum profile (see above).

2) column B contains numbers

No it doesn't

---

4) counted occurrences in column D

How are we supposed to count the numbers in column A that are equal to the letters in column B?

---

5) Result should show digits of column A corresponding to only unique values of column c ..

We wont necessarily reach this point unless you can explain points 2 and 4.

---

To save everyones time, here is sample Google Sheet that we can continue development of the solution:

https://docs.google.com/spreadsheets...gid=2100307022

Please provide more clear explanation regarding points 2 and 4, and we can take it from there.

Column B does NOT contain numbers!!!

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	B
1	AB
2	AB
3	CD
4	EF
5	CD
6	AB
7	EF
8	GH
9	HI
10	JK
11	GH

Sheet: Sheet1

Final solution...

Sort unique in C2:
=SORT(UNIQUE(B2:B),1,1)

Get count of occurrences in D2:
=ARRAYFORMULA(IF(C2:C="",,COUNTIF(B2:B,C2:C)))

1st yymm in E2:
=BYROW($C$2:$C,LAMBDA(serial,IF(serial="",,ARRAYFORMULA(INDEX(SPLIT(TEXTJOIN(", ", TRUE, IF($B$2:$B=serial, $A$2:$A, "")),", "),1,1)))))

2nd yymm in F2:
=BYROW($C$2:$C,LAMBDA(serial,IF(serial="",,ARRAYFORMULA(IFERROR(INDEX(SPLIT(TEXTJOIN(", ", TRUE, IF($B$2:$B=serial, $A$2:$A, "")),", "),1,2))))))


subsequent yymm in G2 onwards, simply change the last digit 2 to get the next index, so G2 is:
=BYROW($C$2:$C,LAMBDA(serial,IF(serial="",,ARRAYFORMULA(IFERROR(INDEX(SPLIT(TEXTJOIN(", ", TRUE, IF($B$2:$B=serial, $A$2:$A, "")),", "),1,3))))))


Due to the amount of data being processed, these arrayformulas stake a long time to get the results... so be patient when waiting for results to update.

Also, in the source data there is nothing with more than 3 yymm, so the 4th and subsequent appear empty.

yymm.jpg

Originally Posted by YOGESHP

Thanks for help , formula worked ..

When you have a working solution, it is customary to mark the topic as "Solved".

Also, Thank you for the reps, it is very appreciated.