Counting the unique visit of customers

M

mohaguru

New Member
Joined
Apr 5, 2014
Messages
11
Need Help from excell expart, please....
In my below data, I want to find out according to column "B" - [Date], column "D" - [CustomerName] how many uniques time visited to certain period of date. My data is in a table and its filtered through column "B" - [Date] and I want my result to show in Column "F". Ignore the result what you have seen in column "F", thats what I type manualy. Column "A" and Column "C" can be ignored as well if necessary.

[TABLE="width: 655"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]BARSHA[/TD]
[TD]27/03/19[/TD]
[TD]FNC[/TD]
[TD]KHAMIS[/TD]
[TD]64.00[/TD]
[TD]1 Time[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]BARSHA[/TD]
[TD]27/03/19[/TD]
[TD]FNC[/TD]
[TD]KHAMIS[/TD]
[TD]105.00[/TD]
[TD]1 Time[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]BARSHA[/TD]
[TD]27/03/19[/TD]
[TD]FNC[/TD]
[TD]PRADEEP[/TD]
[TD]279.00[/TD]
[TD]1 Time[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]BARSHA[/TD]
[TD]27/03/19[/TD]
[TD]FNC[/TD]
[TD]PRADEEP[/TD]
[TD]44.00[/TD]
[TD]1 Time[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]PANKAJ[/TD]
[TD]27/03/19[/TD]
[TD]FNC[/TD]
[TD]PRADEEP[/TD]
[TD]47.00[/TD]
[TD]1 Time[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]BARSHA[/TD]
[TD]27/03/19[/TD]
[TD]FNC[/TD]
[TD]SHERW[/TD]
[TD]312.00[/TD]
[TD]2 Times[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]FLOR[/TD]
[TD]27/03/19[/TD]
[TD]FNC[/TD]
[TD]SP UL ALAM[/TD]
[TD]279.00[/TD]
[TD]1 Time[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]JOCELYN[/TD]
[TD]27/03/19[/TD]
[TD]FNC[/TD]
[TD]HASSAN[/TD]
[TD]253.00[/TD]
[TD]5 Times[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]JOCELYN[/TD]
[TD]27/03/19[/TD]
[TD]FNC[/TD]
[TD]HASSAN[/TD]
[TD]197.00[/TD]
[TD]5 Times[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]BARSHA[/TD]
[TD]27/03/19[/TD]
[TD]FNC[/TD]
[TD]SULIMAN[/TD]
[TD]565.00[/TD]
[TD]1 Time[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]JEEVAN[/TD]
[TD]28/03/19[/TD]
[TD]VIP[/TD]
[TD]MOHAMMED EMDADULHAQUE[/TD]
[TD]47.00[/TD]
[TD]2 Times[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]JEEVAN[/TD]
[TD]28/03/19[/TD]
[TD]VIP[/TD]
[TD]MOHAMMED EMDADULHAQUE[/TD]
[TD]53.00[/TD]
[TD]2 Times[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]BARSHA[/TD]
[TD]28/03/19[/TD]
[TD]FNC[/TD]
[TD]MAHIR[/TD]
[TD]98.00[/TD]
[TD]1 Time[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]JEEVAN[/TD]
[TD]28/03/19[/TD]
[TD]VIP[/TD]
[TD]PURAKOTT SANTHOSH[/TD]
[TD]317.00[/TD]
[TD]1 time[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]FLOR[/TD]
[TD]28/03/19[/TD]
[TD]FNC[/TD]
[TD]MOHAMED[/TD]
[TD]279.00[/TD]
[TD]2 Times[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]FLOR[/TD]
[TD]28/03/19[/TD]
[TD]FNC[/TD]
[TD]RAFIQUE[/TD]
[TD]186.00[/TD]
[TD]4 Times[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]BARSHA[/TD]
[TD]28/03/19[/TD]
[TD]FNC[/TD]
[TD]AMAN HAMID[/TD]
[TD]220.00[/TD]
[TD]1 Time[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]BARSHA[/TD]
[TD]28/03/19[/TD]
[TD]FNC[/TD]
[TD]AMAN HAMID[/TD]
[TD]141.00[/TD]
[TD]1 Time[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]FARHAD[/TD]
[TD]28/03/19[/TD]
[TD]FNC[/TD]
[TD]THEAR NIHAD[/TD]
[TD]664.00[/TD]
[TD]1 Time[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]LEIZEL[/TD]
[TD]28/03/19[/TD]
[TD]VIP[/TD]
[TD]SREENIVAS KURRU[/TD]
[TD]43.00[/TD]
[TD]1 Time[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]LEIZEL[/TD]
[TD]28/03/19[/TD]
[TD]VIP[/TD]
[TD]SREENIVAS KURRU[/TD]
[TD]47.00[/TD]
[TD]1 Time[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]FLOR[/TD]
[TD]28/03/19[/TD]
[TD]FNC[/TD]
[TD]AHMED ALY MOHAMED[/TD]
[TD]897.00[/TD]
[TD]1 Time[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]BARSHA[/TD]
[TD]28/03/19[/TD]
[TD]FNC[/TD]
[TD]SHERW[/TD]
[TD]1,360.00[/TD]
[TD]2 Times[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]BARSHA[/TD]
[TD]28/03/19[/TD]
[TD]FNC[/TD]
[TD]SULEYMAN[/TD]
[TD]1,324.00[/TD]
[TD]3 Times[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]BARSHA[/TD]
[TD]28/03/19[/TD]
[TD]FNC[/TD]
[TD]YASIN TURKMEN[/TD]
[TD]141.00[/TD]
[TD]1 Time[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]BARSHA[/TD]
[TD]28/03/19[/TD]
[TD]FNC[/TD]
[TD]WASIQUE AHMED[/TD]
[TD]300.00[/TD]
[TD]1 Time[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]ram[/TD]
[TD]28/03/19[/TD]
[TD]VIP[/TD]
[TD]JIBRAN NASIF AHAMED[/TD]
[TD]215.00[/TD]
[TD]3 Times[/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]BARSHA[/TD]
[TD]28/03/19[/TD]
[TD]FNC[/TD]
[TD]MAHER[/TD]
[TD]279.00[/TD]
[TD]1 Time[/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD]BARSHA[/TD]
[TD]28/03/19[/TD]
[TD]FNC[/TD]
[TD]MAHER[/TD]
[TD]279.00[/TD]
[TD]1 Time[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]ram[/TD]
[TD]28/03/19[/TD]
[TD]VIP[/TD]
[TD]MOHAMED JALAL[/TD]
[TD]897.00[/TD]
[TD]1 Time[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]JOCELYN[/TD]
[TD]28/03/19[/TD]
[TD]FNC[/TD]
[TD]BASSAM[/TD]
[TD]1,030.00[/TD]
[TD]1 Time[/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]LEIZEL[/TD]
[TD]28/03/19[/TD]
[TD]VIP[/TD]
[TD]FATTHY B[/TD]
[TD]2,139.00[/TD]
[TD]4 Times[/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD]LEIZEL[/TD]
[TD]28/03/19[/TD]
[TD]VIP[/TD]
[TD]FATTHY B[/TD]
[TD]197.00[/TD]
[TD]4 Times[/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]LEIZEL[/TD]
[TD]28/03/19[/TD]
[TD]VIP[/TD]
[TD]MOHAMMED FATHY[/TD]
[TD]382.00[/TD]
[TD]4 Times[/TD]
[/TR]
</tbody>[/TABLE]
* my data has more than 3000 rows, above is an example only.

Your help will highly appreciate.
Thanks
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
Hello,

Just insert a Pivot table ...

Hope this will help
 
Upvote 0
two ways

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]source[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]PowerQuery[/td][td][/td][td][/td][td][/td][td]PivotTable[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td=bgcolor:#5B9BD5]Column2[/td][td=bgcolor:#5B9BD5]Column3[/td][td=bgcolor:#5B9BD5]Column4[/td][td=bgcolor:#5B9BD5]Column5[/td][td][/td][td=bgcolor:#70AD47]Column2[/td][td=bgcolor:#70AD47]Column4[/td][td=bgcolor:#70AD47]Count[/td][td][/td][td=bgcolor:#DDEBF7]Column2[/td][td=bgcolor:#DDEBF7]Column4[/td][td=bgcolor:#DDEBF7]Count of Column4[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]BARSHA[/td][td=bgcolor:#DDEBF7]
27/03/2019​
[/td][td=bgcolor:#DDEBF7]FNC[/td][td=bgcolor:#DDEBF7]KHAMIS[/td][td=bgcolor:#DDEBF7]
64​
[/td][td][/td][td=bgcolor:#E2EFDA]
27/03/2019​
[/td][td=bgcolor:#E2EFDA]HASSAN[/td][td=bgcolor:#E2EFDA]
2​
[/td][td][/td][td]
27/03/2019​
[/td][td]HASSAN[/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]BARSHA[/td][td]
27/03/2019​
[/td][td]FNC[/td][td]KHAMIS[/td][td]
105​
[/td][td][/td][td]
27/03/2019​
[/td][td]KHAMIS[/td][td]
2​
[/td][td][/td][td]
27/03/2019​
[/td][td]KHAMIS[/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]BARSHA[/td][td=bgcolor:#DDEBF7]
27/03/2019​
[/td][td=bgcolor:#DDEBF7]FNC[/td][td=bgcolor:#DDEBF7]PRADEEP[/td][td=bgcolor:#DDEBF7]
279​
[/td][td][/td][td=bgcolor:#E2EFDA]
27/03/2019​
[/td][td=bgcolor:#E2EFDA]PRADEEP[/td][td=bgcolor:#E2EFDA]
3​
[/td][td][/td][td]
27/03/2019​
[/td][td]PRADEEP[/td][td]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]BARSHA[/td][td]
27/03/2019​
[/td][td]FNC[/td][td]PRADEEP[/td][td]
44​
[/td][td][/td][td]
27/03/2019​
[/td][td]SHERW[/td][td]
1​
[/td][td][/td][td]
27/03/2019​
[/td][td]SHERW[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]PANKAJ[/td][td=bgcolor:#DDEBF7]
27/03/2019​
[/td][td=bgcolor:#DDEBF7]FNC[/td][td=bgcolor:#DDEBF7]PRADEEP[/td][td=bgcolor:#DDEBF7]
47​
[/td][td][/td][td=bgcolor:#E2EFDA]
27/03/2019​
[/td][td=bgcolor:#E2EFDA]SP UL ALAM[/td][td=bgcolor:#E2EFDA]
1​
[/td][td][/td][td]
27/03/2019​
[/td][td]SP UL ALAM[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]BARSHA[/td][td]
27/03/2019​
[/td][td]FNC[/td][td]SHERW[/td][td]
312​
[/td][td][/td][td]
27/03/2019​
[/td][td]SULIMAN[/td][td]
1​
[/td][td][/td][td]
27/03/2019​
[/td][td]SULIMAN[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]FLOR[/td][td=bgcolor:#DDEBF7]
27/03/2019​
[/td][td=bgcolor:#DDEBF7]FNC[/td][td=bgcolor:#DDEBF7]SP UL ALAM[/td][td=bgcolor:#DDEBF7]
279​
[/td][td][/td][td=bgcolor:#E2EFDA]
28/03/2019​
[/td][td=bgcolor:#E2EFDA]AHMED ALY MOHAMED[/td][td=bgcolor:#E2EFDA]
1​
[/td][td][/td][td]
28/03/2019​
[/td][td]AHMED ALY MOHAMED[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]JOCELYN[/td][td]
27/03/2019​
[/td][td]FNC[/td][td]HASSAN[/td][td]
253​
[/td][td][/td][td]
28/03/2019​
[/td][td]AMAN HAMID[/td][td]
2​
[/td][td][/td][td]
28/03/2019​
[/td][td]AMAN HAMID[/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]JOCELYN[/td][td=bgcolor:#DDEBF7]
27/03/2019​
[/td][td=bgcolor:#DDEBF7]FNC[/td][td=bgcolor:#DDEBF7]HASSAN[/td][td=bgcolor:#DDEBF7]
197​
[/td][td][/td][td=bgcolor:#E2EFDA]
28/03/2019​
[/td][td=bgcolor:#E2EFDA]BASSAM[/td][td=bgcolor:#E2EFDA]
1​
[/td][td][/td][td]
28/03/2019​
[/td][td]BASSAM[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]BARSHA[/td][td]
27/03/2019​
[/td][td]FNC[/td][td]SULIMAN[/td][td]
565​
[/td][td][/td][td]
28/03/2019​
[/td][td]FATTHY B[/td][td]
2​
[/td][td][/td][td]
28/03/2019​
[/td][td]FATTHY B[/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]JEEVAN[/td][td=bgcolor:#DDEBF7]
28/03/2019​
[/td][td=bgcolor:#DDEBF7]VIP[/td][td=bgcolor:#DDEBF7]MOHAMMED EMDADULHAQUE[/td][td=bgcolor:#DDEBF7]
47​
[/td][td][/td][td=bgcolor:#E2EFDA]
28/03/2019​
[/td][td=bgcolor:#E2EFDA]JIBRAN NASIF AHAMED[/td][td=bgcolor:#E2EFDA]
1​
[/td][td][/td][td]
28/03/2019​
[/td][td]JIBRAN NASIF AHAMED[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]JEEVAN[/td][td]
28/03/2019​
[/td][td]VIP[/td][td]MOHAMMED EMDADULHAQUE[/td][td]
53​
[/td][td][/td][td]
28/03/2019​
[/td][td]MAHER[/td][td]
2​
[/td][td][/td][td]
28/03/2019​
[/td][td]MAHER[/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]BARSHA[/td][td=bgcolor:#DDEBF7]
28/03/2019​
[/td][td=bgcolor:#DDEBF7]FNC[/td][td=bgcolor:#DDEBF7]MAHIR[/td][td=bgcolor:#DDEBF7]
98​
[/td][td][/td][td=bgcolor:#E2EFDA]
28/03/2019​
[/td][td=bgcolor:#E2EFDA]MAHIR[/td][td=bgcolor:#E2EFDA]
1​
[/td][td][/td][td]
28/03/2019​
[/td][td]MAHIR[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]JEEVAN[/td][td]
28/03/2019​
[/td][td]VIP[/td][td]PURAKOTT SANTHOSH[/td][td]
317​
[/td][td][/td][td]
28/03/2019​
[/td][td]MOHAMED[/td][td]
1​
[/td][td][/td][td]
28/03/2019​
[/td][td]MOHAMED[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]FLOR[/td][td=bgcolor:#DDEBF7]
28/03/2019​
[/td][td=bgcolor:#DDEBF7]FNC[/td][td=bgcolor:#DDEBF7]MOHAMED[/td][td=bgcolor:#DDEBF7]
279​
[/td][td][/td][td=bgcolor:#E2EFDA]
28/03/2019​
[/td][td=bgcolor:#E2EFDA]MOHAMED JALAL[/td][td=bgcolor:#E2EFDA]
1​
[/td][td][/td][td]
28/03/2019​
[/td][td]MOHAMED JALAL[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]FLOR[/td][td]
28/03/2019​
[/td][td]FNC[/td][td]RAFIQUE[/td][td]
186​
[/td][td][/td][td]
28/03/2019​
[/td][td]MOHAMMED EMDADULHAQUE[/td][td]
2​
[/td][td][/td][td]
28/03/2019​
[/td][td]MOHAMMED EMDADULHAQUE[/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]BARSHA[/td][td=bgcolor:#DDEBF7]
28/03/2019​
[/td][td=bgcolor:#DDEBF7]FNC[/td][td=bgcolor:#DDEBF7]AMAN HAMID[/td][td=bgcolor:#DDEBF7]
220​
[/td][td][/td][td=bgcolor:#E2EFDA]
28/03/2019​
[/td][td=bgcolor:#E2EFDA]MOHAMMED FATHY[/td][td=bgcolor:#E2EFDA]
1​
[/td][td][/td][td]
28/03/2019​
[/td][td]MOHAMMED FATHY[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]BARSHA[/td][td]
28/03/2019​
[/td][td]FNC[/td][td]AMAN HAMID[/td][td]
141​
[/td][td][/td][td]
28/03/2019​
[/td][td]PURAKOTT SANTHOSH[/td][td]
1​
[/td][td][/td][td]
28/03/2019​
[/td][td]PURAKOTT SANTHOSH[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]FARHAD[/td][td=bgcolor:#DDEBF7]
28/03/2019​
[/td][td=bgcolor:#DDEBF7]FNC[/td][td=bgcolor:#DDEBF7]THEAR NIHAD[/td][td=bgcolor:#DDEBF7]
664​
[/td][td][/td][td=bgcolor:#E2EFDA]
28/03/2019​
[/td][td=bgcolor:#E2EFDA]RAFIQUE[/td][td=bgcolor:#E2EFDA]
1​
[/td][td][/td][td]
28/03/2019​
[/td][td]RAFIQUE[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]LEIZEL[/td][td]
28/03/2019​
[/td][td]VIP[/td][td]SREENIVAS KURRU[/td][td]
43​
[/td][td][/td][td]
28/03/2019​
[/td][td]SHERW[/td][td]
1​
[/td][td][/td][td]
28/03/2019​
[/td][td]SHERW[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]LEIZEL[/td][td=bgcolor:#DDEBF7]
28/03/2019​
[/td][td=bgcolor:#DDEBF7]VIP[/td][td=bgcolor:#DDEBF7]SREENIVAS KURRU[/td][td=bgcolor:#DDEBF7]
47​
[/td][td][/td][td=bgcolor:#E2EFDA]
28/03/2019​
[/td][td=bgcolor:#E2EFDA]SREENIVAS KURRU[/td][td=bgcolor:#E2EFDA]
2​
[/td][td][/td][td]
28/03/2019​
[/td][td]SREENIVAS KURRU[/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]FLOR[/td][td]
28/03/2019​
[/td][td]FNC[/td][td]AHMED ALY MOHAMED[/td][td]
897​
[/td][td][/td][td]
28/03/2019​
[/td][td]SULEYMAN[/td][td]
1​
[/td][td][/td][td]
28/03/2019​
[/td][td]SULEYMAN[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]BARSHA[/td][td=bgcolor:#DDEBF7]
28/03/2019​
[/td][td=bgcolor:#DDEBF7]FNC[/td][td=bgcolor:#DDEBF7]SHERW[/td][td=bgcolor:#DDEBF7]
1,360.00​
[/td][td][/td][td=bgcolor:#E2EFDA]
28/03/2019​
[/td][td=bgcolor:#E2EFDA]THEAR NIHAD[/td][td=bgcolor:#E2EFDA]
1​
[/td][td][/td][td]
28/03/2019​
[/td][td]THEAR NIHAD[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]BARSHA[/td][td]
28/03/2019​
[/td][td]FNC[/td][td]SULEYMAN[/td][td]
1,324.00​
[/td][td][/td][td]
28/03/2019​
[/td][td]WASIQUE AHMED[/td][td]
1​
[/td][td][/td][td]
28/03/2019​
[/td][td]WASIQUE AHMED[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]BARSHA[/td][td=bgcolor:#DDEBF7]
28/03/2019​
[/td][td=bgcolor:#DDEBF7]FNC[/td][td=bgcolor:#DDEBF7]YASIN TURKMEN[/td][td=bgcolor:#DDEBF7]
141​
[/td][td][/td][td=bgcolor:#E2EFDA]
28/03/2019​
[/td][td=bgcolor:#E2EFDA]YASIN TURKMEN[/td][td=bgcolor:#E2EFDA]
1​
[/td][td][/td][td]
28/03/2019​
[/td][td]YASIN TURKMEN[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]BARSHA[/td][td]
28/03/2019​
[/td][td]FNC[/td][td]WASIQUE AHMED[/td][td]
300​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]ram[/td][td=bgcolor:#DDEBF7]
28/03/2019​
[/td][td=bgcolor:#DDEBF7]VIP[/td][td=bgcolor:#DDEBF7]JIBRAN NASIF AHAMED[/td][td=bgcolor:#DDEBF7]
215​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]BARSHA[/td][td]
28/03/2019​
[/td][td]FNC[/td][td]MAHER[/td][td]
279​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]BARSHA[/td][td=bgcolor:#DDEBF7]
28/03/2019​
[/td][td=bgcolor:#DDEBF7]FNC[/td][td=bgcolor:#DDEBF7]MAHER[/td][td=bgcolor:#DDEBF7]
279​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]ram[/td][td]
28/03/2019​
[/td][td]VIP[/td][td]MOHAMED JALAL[/td][td]
897​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]JOCELYN[/td][td=bgcolor:#DDEBF7]
28/03/2019​
[/td][td=bgcolor:#DDEBF7]FNC[/td][td=bgcolor:#DDEBF7]BASSAM[/td][td=bgcolor:#DDEBF7]
1,030.00​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]LEIZEL[/td][td]
28/03/2019​
[/td][td]VIP[/td][td]FATTHY B[/td][td]
2,139.00​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]LEIZEL[/td][td=bgcolor:#DDEBF7]
28/03/2019​
[/td][td=bgcolor:#DDEBF7]VIP[/td][td=bgcolor:#DDEBF7]FATTHY B[/td][td=bgcolor:#DDEBF7]
197​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]LEIZEL[/td][td]
28/03/2019​
[/td][td]VIP[/td][td]MOHAMMED FATHY[/td][td]
382​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
 
Upvote 0
sandy666 said:
two ways

[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]source[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PowerQuery[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PivotTable[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column1[/COLOR][/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column2[/COLOR][/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column3[/COLOR][/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column4[/COLOR][/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column5[/COLOR][/TD]
[TD][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column2[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column4[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Count[/COLOR][/TD]
[TD][/TD]
[TD="bgcolor: #DDEBF7"]Column2[/TD]
[TD="bgcolor: #DDEBF7"]Column4[/TD]
[TD="bgcolor: #DDEBF7"]Count of Column4[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]BARSHA[/TD]
[TD="bgcolor: #DDEBF7"]
27/03/2019​
[/TD]
[TD="bgcolor: #DDEBF7"]FNC[/TD]
[TD="bgcolor: #DDEBF7"]KHAMIS[/TD]
[TD="bgcolor: #DDEBF7"]
64​
[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]
27/03/2019​
[/TD]
[TD="bgcolor: #E2EFDA"]HASSAN[/TD]
[TD="bgcolor: #E2EFDA"]
2​
[/TD]
[TD][/TD]
[TD]
27/03/2019​
[/TD]
[TD]HASSAN[/TD]
[TD]
2​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]BARSHA[/TD]
[TD]
27/03/2019​
[/TD]
[TD]FNC[/TD]
[TD]KHAMIS[/TD]
[TD]
105​
[/TD]
[TD][/TD]
[TD]
27/03/2019​
[/TD]
[TD]KHAMIS[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD]
27/03/2019​
[/TD]
[TD]KHAMIS[/TD]
[TD]
2​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]BARSHA[/TD]
[TD="bgcolor: #DDEBF7"]
27/03/2019​
[/TD]
[TD="bgcolor: #DDEBF7"]FNC[/TD]
[TD="bgcolor: #DDEBF7"]PRADEEP[/TD]
[TD="bgcolor: #DDEBF7"]
279​
[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]
27/03/2019​
[/TD]
[TD="bgcolor: #E2EFDA"]PRADEEP[/TD]
[TD="bgcolor: #E2EFDA"]
3​
[/TD]
[TD][/TD]
[TD]
27/03/2019​
[/TD]
[TD]PRADEEP[/TD]
[TD]
3​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]BARSHA[/TD]
[TD]
27/03/2019​
[/TD]
[TD]FNC[/TD]
[TD]PRADEEP[/TD]
[TD]
44​
[/TD]
[TD][/TD]
[TD]
27/03/2019​
[/TD]
[TD]SHERW[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD]
27/03/2019​
[/TD]
[TD]SHERW[/TD]
[TD]
1​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]PANKAJ[/TD]
[TD="bgcolor: #DDEBF7"]
27/03/2019​
[/TD]
[TD="bgcolor: #DDEBF7"]FNC[/TD]
[TD="bgcolor: #DDEBF7"]PRADEEP[/TD]
[TD="bgcolor: #DDEBF7"]
47​
[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]
27/03/2019​
[/TD]
[TD="bgcolor: #E2EFDA"]SP UL ALAM[/TD]
[TD="bgcolor: #E2EFDA"]
1​
[/TD]
[TD][/TD]
[TD]
27/03/2019​
[/TD]
[TD]SP UL ALAM[/TD]
[TD]
1​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]BARSHA[/TD]
[TD]
27/03/2019​
[/TD]
[TD]FNC[/TD]
[TD]SHERW[/TD]
[TD]
312​
[/TD]
[TD][/TD]
[TD]
27/03/2019​
[/TD]
[TD]SULIMAN[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD]
27/03/2019​
[/TD]
[TD]SULIMAN[/TD]
[TD]
1​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]FLOR[/TD]
[TD="bgcolor: #DDEBF7"]
27/03/2019​
[/TD]
[TD="bgcolor: #DDEBF7"]FNC[/TD]
[TD="bgcolor: #DDEBF7"]SP UL ALAM[/TD]
[TD="bgcolor: #DDEBF7"]
279​
[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]
28/03/2019​
[/TD]
[TD="bgcolor: #E2EFDA"]AHMED ALY MOHAMED[/TD]
[TD="bgcolor: #E2EFDA"]
1​
[/TD]
[TD][/TD]
[TD]
28/03/2019​
[/TD]
[TD]AHMED ALY MOHAMED[/TD]
[TD]
1​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]JOCELYN[/TD]
[TD]
27/03/2019​
[/TD]
[TD]FNC[/TD]
[TD]HASSAN[/TD]
[TD]
253​
[/TD]
[TD][/TD]
[TD]
28/03/2019​
[/TD]
[TD]AMAN HAMID[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD]
28/03/2019​
[/TD]
[TD]AMAN HAMID[/TD]
[TD]
2​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]JOCELYN[/TD]
[TD="bgcolor: #DDEBF7"]
27/03/2019​
[/TD]
[TD="bgcolor: #DDEBF7"]FNC[/TD]
[TD="bgcolor: #DDEBF7"]HASSAN[/TD]
[TD="bgcolor: #DDEBF7"]
197​
[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]
28/03/2019​
[/TD]
[TD="bgcolor: #E2EFDA"]BASSAM[/TD]
[TD="bgcolor: #E2EFDA"]
1​
[/TD]
[TD][/TD]
[TD]
28/03/2019​
[/TD]
[TD]BASSAM[/TD]
[TD]
1​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]BARSHA[/TD]
[TD]
27/03/2019​
[/TD]
[TD]FNC[/TD]
[TD]SULIMAN[/TD]
[TD]
565​
[/TD]
[TD][/TD]
[TD]
28/03/2019​
[/TD]
[TD]FATTHY B[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD]
28/03/2019​
[/TD]
[TD]FATTHY B[/TD]
[TD]
2​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]JEEVAN[/TD]
[TD="bgcolor: #DDEBF7"]
28/03/2019​
[/TD]
[TD="bgcolor: #DDEBF7"]VIP[/TD]
[TD="bgcolor: #DDEBF7"]MOHAMMED EMDADULHAQUE[/TD]
[TD="bgcolor: #DDEBF7"]
47​
[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]
28/03/2019​
[/TD]
[TD="bgcolor: #E2EFDA"]JIBRAN NASIF AHAMED[/TD]
[TD="bgcolor: #E2EFDA"]
1​
[/TD]
[TD][/TD]
[TD]
28/03/2019​
[/TD]
[TD]JIBRAN NASIF AHAMED[/TD]
[TD]
1​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]JEEVAN[/TD]
[TD]
28/03/2019​
[/TD]
[TD]VIP[/TD]
[TD]MOHAMMED EMDADULHAQUE[/TD]
[TD]
53​
[/TD]
[TD][/TD]
[TD]
28/03/2019​
[/TD]
[TD]MAHER[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD]
28/03/2019​
[/TD]
[TD]MAHER[/TD]
[TD]
2​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]BARSHA[/TD]
[TD="bgcolor: #DDEBF7"]
28/03/2019​
[/TD]
[TD="bgcolor: #DDEBF7"]FNC[/TD]
[TD="bgcolor: #DDEBF7"]MAHIR[/TD]
[TD="bgcolor: #DDEBF7"]
98​
[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]
28/03/2019​
[/TD]
[TD="bgcolor: #E2EFDA"]MAHIR[/TD]
[TD="bgcolor: #E2EFDA"]
1​
[/TD]
[TD][/TD]
[TD]
28/03/2019​
[/TD]
[TD]MAHIR[/TD]
[TD]
1​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]JEEVAN[/TD]
[TD]
28/03/2019​
[/TD]
[TD]VIP[/TD]
[TD]PURAKOTT SANTHOSH[/TD]
[TD]
317​
[/TD]
[TD][/TD]
[TD]
28/03/2019​
[/TD]
[TD]MOHAMED[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD]
28/03/2019​
[/TD]
[TD]MOHAMED[/TD]
[TD]
1​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]FLOR[/TD]
[TD="bgcolor: #DDEBF7"]
28/03/2019​
[/TD]
[TD="bgcolor: #DDEBF7"]FNC[/TD]
[TD="bgcolor: #DDEBF7"]MOHAMED[/TD]
[TD="bgcolor: #DDEBF7"]
279​
[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]
28/03/2019​
[/TD]
[TD="bgcolor: #E2EFDA"]MOHAMED JALAL[/TD]
[TD="bgcolor: #E2EFDA"]
1​
[/TD]
[TD][/TD]
[TD]
28/03/2019​
[/TD]
[TD]MOHAMED JALAL[/TD]
[TD]
1​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]FLOR[/TD]
[TD]
28/03/2019​
[/TD]
[TD]FNC[/TD]
[TD]RAFIQUE[/TD]
[TD]
186​
[/TD]
[TD][/TD]
[TD]
28/03/2019​
[/TD]
[TD]MOHAMMED EMDADULHAQUE[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD]
28/03/2019​
[/TD]
[TD]MOHAMMED EMDADULHAQUE[/TD]
[TD]
2​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]BARSHA[/TD]
[TD="bgcolor: #DDEBF7"]
28/03/2019​
[/TD]
[TD="bgcolor: #DDEBF7"]FNC[/TD]
[TD="bgcolor: #DDEBF7"]AMAN HAMID[/TD]
[TD="bgcolor: #DDEBF7"]
220​
[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]
28/03/2019​
[/TD]
[TD="bgcolor: #E2EFDA"]MOHAMMED FATHY[/TD]
[TD="bgcolor: #E2EFDA"]
1​
[/TD]
[TD][/TD]
[TD]
28/03/2019​
[/TD]
[TD]MOHAMMED FATHY[/TD]
[TD]
1​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]BARSHA[/TD]
[TD]
28/03/2019​
[/TD]
[TD]FNC[/TD]
[TD]AMAN HAMID[/TD]
[TD]
141​
[/TD]
[TD][/TD]
[TD]
28/03/2019​
[/TD]
[TD]PURAKOTT SANTHOSH[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD]
28/03/2019​
[/TD]
[TD]PURAKOTT SANTHOSH[/TD]
[TD]
1​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]FARHAD[/TD]
[TD="bgcolor: #DDEBF7"]
28/03/2019​
[/TD]
[TD="bgcolor: #DDEBF7"]FNC[/TD]
[TD="bgcolor: #DDEBF7"]THEAR NIHAD[/TD]
[TD="bgcolor: #DDEBF7"]
664​
[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]
28/03/2019​
[/TD]
[TD="bgcolor: #E2EFDA"]RAFIQUE[/TD]
[TD="bgcolor: #E2EFDA"]
1​
[/TD]
[TD][/TD]
[TD]
28/03/2019​
[/TD]
[TD]RAFIQUE[/TD]
[TD]
1​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]LEIZEL[/TD]
[TD]
28/03/2019​
[/TD]
[TD]VIP[/TD]
[TD]SREENIVAS KURRU[/TD]
[TD]
43​
[/TD]
[TD][/TD]
[TD]
28/03/2019​
[/TD]
[TD]SHERW[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD]
28/03/2019​
[/TD]
[TD]SHERW[/TD]
[TD]
1​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]LEIZEL[/TD]
[TD="bgcolor: #DDEBF7"]
28/03/2019​
[/TD]
[TD="bgcolor: #DDEBF7"]VIP[/TD]
[TD="bgcolor: #DDEBF7"]SREENIVAS KURRU[/TD]
[TD="bgcolor: #DDEBF7"]
47​
[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]
28/03/2019​
[/TD]
[TD="bgcolor: #E2EFDA"]SREENIVAS KURRU[/TD]
[TD="bgcolor: #E2EFDA"]
2​
[/TD]
[TD][/TD]
[TD]
28/03/2019​
[/TD]
[TD]SREENIVAS KURRU[/TD]
[TD]
2​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]FLOR[/TD]
[TD]
28/03/2019​
[/TD]
[TD]FNC[/TD]
[TD]AHMED ALY MOHAMED[/TD]
[TD]
897​
[/TD]
[TD][/TD]
[TD]
28/03/2019​
[/TD]
[TD]SULEYMAN[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD]
28/03/2019​
[/TD]
[TD]SULEYMAN[/TD]
[TD]
1​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]BARSHA[/TD]
[TD="bgcolor: #DDEBF7"]
28/03/2019​
[/TD]
[TD="bgcolor: #DDEBF7"]FNC[/TD]
[TD="bgcolor: #DDEBF7"]SHERW[/TD]
[TD="bgcolor: #DDEBF7"]
1,360.00​
[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]
28/03/2019​
[/TD]
[TD="bgcolor: #E2EFDA"]THEAR NIHAD[/TD]
[TD="bgcolor: #E2EFDA"]
1​
[/TD]
[TD][/TD]
[TD]
28/03/2019​
[/TD]
[TD]THEAR NIHAD[/TD]
[TD]
1​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]BARSHA[/TD]
[TD]
28/03/2019​
[/TD]
[TD]FNC[/TD]
[TD]SULEYMAN[/TD]
[TD]
1,324.00​
[/TD]
[TD][/TD]
[TD]
28/03/2019​
[/TD]
[TD]WASIQUE AHMED[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD]
28/03/2019​
[/TD]
[TD]WASIQUE AHMED[/TD]
[TD]
1​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]BARSHA[/TD]
[TD="bgcolor: #DDEBF7"]
28/03/2019​
[/TD]
[TD="bgcolor: #DDEBF7"]FNC[/TD]
[TD="bgcolor: #DDEBF7"]YASIN TURKMEN[/TD]
[TD="bgcolor: #DDEBF7"]
141​
[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]
28/03/2019​
[/TD]
[TD="bgcolor: #E2EFDA"]YASIN TURKMEN[/TD]
[TD="bgcolor: #E2EFDA"]
1​
[/TD]
[TD][/TD]
[TD]
28/03/2019​
[/TD]
[TD]YASIN TURKMEN[/TD]
[TD]
1​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]BARSHA[/TD]
[TD]
28/03/2019​
[/TD]
[TD]FNC[/TD]
[TD]WASIQUE AHMED[/TD]
[TD]
300​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]ram[/TD]
[TD="bgcolor: #DDEBF7"]
28/03/2019​
[/TD]
[TD="bgcolor: #DDEBF7"]VIP[/TD]
[TD="bgcolor: #DDEBF7"]JIBRAN NASIF AHAMED[/TD]
[TD="bgcolor: #DDEBF7"]
215​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]BARSHA[/TD]
[TD]
28/03/2019​
[/TD]
[TD]FNC[/TD]
[TD]MAHER[/TD]
[TD]
279​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]BARSHA[/TD]
[TD="bgcolor: #DDEBF7"]
28/03/2019​
[/TD]
[TD="bgcolor: #DDEBF7"]FNC[/TD]
[TD="bgcolor: #DDEBF7"]MAHER[/TD]
[TD="bgcolor: #DDEBF7"]
279​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]ram[/TD]
[TD]
28/03/2019​
[/TD]
[TD]VIP[/TD]
[TD]MOHAMED JALAL[/TD]
[TD]
897​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]JOCELYN[/TD]
[TD="bgcolor: #DDEBF7"]
28/03/2019​
[/TD]
[TD="bgcolor: #DDEBF7"]FNC[/TD]
[TD="bgcolor: #DDEBF7"]BASSAM[/TD]
[TD="bgcolor: #DDEBF7"]
1,030.00​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]LEIZEL[/TD]
[TD]
28/03/2019​
[/TD]
[TD]VIP[/TD]
[TD]FATTHY B[/TD]
[TD]
2,139.00​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]LEIZEL[/TD]
[TD="bgcolor: #DDEBF7"]
28/03/2019​
[/TD]
[TD="bgcolor: #DDEBF7"]VIP[/TD]
[TD="bgcolor: #DDEBF7"]FATTHY B[/TD]
[TD="bgcolor: #DDEBF7"]
197​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]LEIZEL[/TD]
[TD]
28/03/2019​
[/TD]
[TD]VIP[/TD]
[TD]MOHAMMED FATHY[/TD]
[TD]
382​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

I will try yours one, but is it possible with any formulas (actually I dont want my data to be re-arranged, my date will change in from weekly basis)
 
Upvote 0
first: don't quote whole post, it doesn't make any sense
second: if your data will change just refresh eg. PivotTable and you'll get new result (source should be an Excel Table not a range)
third: I don't know any formula for this so you need to wait for someone else
 
Last edited:
Upvote 0
Thank you sandy666, for your valuable time. Actually your powerQuery and PivotTable is not giving me what I want. May be what I want its not clear in my previous post. I need My original data to remain same, Only I want next to original data which I called Column "F" to mention how many times this customer visited to my shop. I dont want to merge his duplicate visit in one row. If in a particular date the customer visit 4/5 times It should count 1 for those days all entry, but if different date, the count should be different and visit count to be same for this respective customer each entry row.
 
Upvote 0
Maybe something like this array formula.
Must be entered with CTRL-SHIFT-ENTER. Then drag formula down as needed.
Excel Workbook
ABCDEF
1BARSHA3/27/2019FNCKHAMIS641
2BARSHA3/27/2019FNCKHAMIS1051
3BARSHA3/27/2019FNCPRADEEP2791
4BARSHA3/27/2019FNCPRADEEP441
5PANKAJ3/27/2019FNCPRADEEP471
6BARSHA3/27/2019FNCSHERW3122
7FLOR3/27/2019FNCSP UL ALAM2791
8JOCELYN3/27/2019FNCHASSAN2531
9JOCELYN3/27/2019FNCHASSAN1971
10BARSHA3/27/2019FNCSULIMAN5651
11JEEVAN3/28/2019VIPMOHAMMED EMDADULHAQUE471
12JEEVAN3/28/2019VIPMOHAMMED EMDADULHAQUE531
13BARSHA3/28/2019FNCMAHIR981
14JEEVAN3/28/2019VIPPURAKOTT SANTHOSH3171
15FLOR3/28/2019FNCMOHAMED2791
16FLOR3/28/2019FNCRAFIQUE1861
17BARSHA3/28/2019FNCAMAN HAMID2201
18BARSHA3/28/2019FNCAMAN HAMID1411
19FARHAD3/28/2019FNCTHEAR NIHAD6641
20LEIZEL3/28/2019VIPSREENIVAS KURRU431
21LEIZEL3/28/2019VIPSREENIVAS KURRU471
22FLOR3/28/2019FNCAHMED ALY MOHAMED8971
23BARSHA3/28/2019FNCSHERW1,360.002
24BARSHA3/28/2019FNCSULEYMAN1,324.001
25BARSHA3/28/2019FNCYASIN TURKMEN1411
26BARSHA3/28/2019FNCWASIQUE AHMED3001
27ram3/28/2019VIPJIBRAN NASIF AHAMED2151
28BARSHA3/28/2019FNCMAHER2791
29BARSHA3/28/2019FNCMAHER2791
30ram3/28/2019VIPMOHAMED JALAL8971
31JOCELYN3/28/2019FNCBASSAM1,030.001
32LEIZEL3/28/2019VIPFATTHY B2,139.001
33LEIZEL3/28/2019VIPFATTHY B1971
34LEIZEL3/28/2019VIPMOHAMMED FATHY3821
Sheet
 
Upvote 0
Avoiding the Pivot Table ... and using an Array formula for a large database ... might cost you a bit in performance ...
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

Share this page

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
Back
Top