how to create a unique list & sum accordingly

[sanjeev]

Hi
I m herewith attaching my file for reference. What i want to create a unique list from given data and sum of these values.
Kindly check the file and you will understand easily.

Thanks in Anticipation
Kind Regards,
Sanjeev

 

[SirJB7]

Hi, sanjeev!

Give a look at the uploaded file.

It creates a single list with duplicates, the a list with unique values including blank, and lastly a unique list of non blank values (WorkList, IntermediateList and TargetList respectively) from the red border range (SourceTable). Rows shaded in orange indicate up to where they have formulas copied. What's still missing is the final count... homework! (I hope that for you )).

a) Worksheet Sheet36

- Named ranges:

SourceTable: $F$5:$ES12
it's a fixed range since I couldn't find any clue to make it dynamic, which I prefer

b) Worksheet Hoja1

- Named ranges:

WorkList: =DESREF(Hoja1!$A$2;;;FILAS(SourceTable)*COLUMNAS(SourceTable)/3) -----> in english: =OFFSET(Hoja1!$A$2,,,ROWS(SourceTable)*COLUMNS(SourceTable)/3)

IntermediateList: =DESREF(WorkList;;1) -----> in english: =OFFSET(WorkList,,1)

TargetList: =DESREF(WorkList;;2;CONTAR.SI(IntermediateList;"> ")) ------> in english: =OFFSET(WorkList,,2,COUNTIF(IntermediateList,"> "))

CountList: =DESREF(TargetList;;1) -----> in english: =OFFSET(TargetList,,1)

- Formulas:

A2: =SI(FILA()-1<=FILAS(SourceTable)*COLUMNAS(SourceTable)/3;SI(INDICE(SourceTable;RESIDUO(FILA()-2;FILAS(SourceTable))+1;(ENTERO((FILA()-2+FILAS(SourceTable))/FILAS(SourceTable))-1)*3+1)="";"";INDICE(SourceTable;RESIDUO(FILA()-2;FILAS(SourceTable))+1;(ENTERO((FILA()-2+FILAS(SourceTable))/FILAS(SourceTable))-1)*3+1));"") -----> in english: =IF(ROW()-1<=ROWS(SourceTable)*COLUMNS(SourceTable)/3,IF(INDEX(SourceTable,MOD(ROW()-2,ROWS(SourceTable))+1,(INT((ROW()-2+ROWS(SourceTable))/ROWS(SourceTable))-1)*3+1)="","",INDEX(SourceTable,MOD(ROW()-2,ROWS(SourceTable))+1,(INT((ROW()-2+ROWS(SourceTable))/ROWS(SourceTable))-1)*3+1)),"")

B2: =SI.ERROR(INDICE(WorkList;COINCIDIR(0;INDICE(CONTAR.SI(B$1:B1;WorkList);0;0);0));"") -----> in english: =IFERROR(INDEX(WorkList,MATCH(0,INDEX(COUNTIF(B$1:B1,WorkList),0,0),0)),"")

C2: =INDICE(IntermediateList;FILA()-1+CONTAR.SI(B$2:B2;"")) -----> in english: =INDEX(IntermediateList,ROW()-1+COUNTIF(B$2:B2,""))

D2: Not yet... pending!

Just advise if any issue.

Regards!

 

[sanjeev]

Hi, sanjeev!

Give a look at the uploaded file.

It creates a single list with duplicates, the a list with unique values including blank, and lastly a unique list of non blank values (WorkList, IntermediateList and TargetList respectively) from the red border range (SourceTable). Rows shaded in orange indicate up to where they have formulas copied. What's still missing is the final count... homework! (I hope that for you )).

a) Worksheet Sheet36

- Named ranges:

SourceTable: $F$5:$ES12
it's a fixed range since I couldn't find any clue to make it dynamic, which I prefer

b) Worksheet Hoja1

- Named ranges:

WorkList: =DESREF(Hoja1!$A$2;;;FILAS(SourceTable)*COLUMNAS(SourceTable)/3) -----> in english: =OFFSET(Hoja1!$A$2,,,ROWS(SourceTable)*COLUMNS(SourceTable)/3)

IntermediateList: =DESREF(WorkList;;1) -----> in english: =OFFSET(WorkList,,1)

TargetList: =DESREF(WorkList;;2;CONTAR.SI(IntermediateList;"> ")) ------> in english: =OFFSET(WorkList,,2,COUNTIF(IntermediateList,"> "))

CountList: =DESREF(TargetList;;1) -----> in english: =OFFSET(TargetList,,1)

- Formulas:

A2: =SI(FILA()-1<=FILAS(SourceTable)*COLUMNAS(SourceTable)/3;SI(INDICE(SourceTable;RESIDUO(FILA()-2;FILAS(SourceTable))+1;(ENTERO((FILA()-2+FILAS(SourceTable))/FILAS(SourceTable))-1)*3+1)="";"";INDICE(SourceTable;RESIDUO(FILA()-2;FILAS(SourceTable))+1;(ENTERO((FILA()-2+FILAS(SourceTable))/FILAS(SourceTable))-1)*3+1));"") -----> in english: =IF(ROW()-1<=ROWS(SourceTable)*COLUMNS(SourceTable)/3,IF(INDEX(SourceTable,MOD(ROW()-2,ROWS(SourceTable))+1,(INT((ROW()-2+ROWS(SourceTable))/ROWS(SourceTable))-1)*3+1)="","",INDEX(SourceTable,MOD(ROW()-2,ROWS(SourceTable))+1,(INT((ROW()-2+ROWS(SourceTable))/ROWS(SourceTable))-1)*3+1)),"")

B2: =SI.ERROR(INDICE(WorkList;COINCIDIR(0;INDICE(CONTAR.SI(B$1:B1;WorkList);0;0);0));"") -----> in english: =IFERROR(INDEX(WorkList,MATCH(0,INDEX(COUNTIF(B$1:B1,WorkList),0,0),0)),"")

C2: =INDICE(IntermediateList;FILA()-1+CONTAR.SI(B$2:B2;"")) -----> in english: =INDEX(IntermediateList,ROW()-1+COUNTIF(B$2:B2,""))

D2: Not yet... pending!

Just advise if any issue.

Regards!

Thanks you very much ...
But still I want to sum the values of unique names in list.... Or Is there any simple way to do it
Kindly advice....
Regards
Sanjeev

 

[Somendra Misra]

Hi Sanjeev....I saw your post yesterday. But couldnt figure out how to give a simple soultion for your problem & I think the reason is the layout of you data. If you can change your layout and use pivot table than I think you can achieve this easily.

Regards!

 

[SirJB7]

Hi, sanjeev!
I know that your goal is that, but it happens that I don't find an easy way to do it with the workbook structure that you have. So I decided to post the file up to where I arrived (D column is pending, for the time being) just in case anybody who read this and has the correct musa aligned with the proper stars would end the job.
Regards!

 

[sanjeev]

Hi Sanjeev....I saw your post yesterday. But couldnt figure out how to give a simple soultion for your problem & I think the reason is the layout of you data. If you can change your layout and use pivot table than I think you can achieve this easily.

Regards!

Can you please do the same for me for understanding as I m trying but all in vain.
Thanks