Help to Index unique records in Multiple columns

darrenmccoy

darrenmccoy

New Member
Joined
Mar 26, 2019
Messages
37
Office Version
  1. 2019
Hi Experts, Could you help me with formulas to index data where there are duplicates in multiple columns?
I am trying to find counts of all unique records and tabulate it.
Here is a sample of the data to be indexed. The found records are UID from a CRM.

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 110px"><col width="122"><col width="100"></colgroup><tbody>[TR]
[TD="bgcolor: #FFF2CC"]NAME[/TD]
[TD="bgcolor: #FFF2CC"]PHONE[/TD]
[TD="bgcolor: #FFF2CC"]EMAIL[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFF2CC"]NOT FOUND[/TD]
[TD="bgcolor: #FFF2CC"]0036F00003EAyMl[/TD]
[TD="bgcolor: #FFF2CC"]0036F00003EAyMl[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFF2CC"]NOT FOUND[/TD]
[TD="bgcolor: #FFF2CC"]NOT FOUND[/TD]
[TD="bgcolor: #FFF2CC"]0036F00003KKCCU[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFF2CC"]NOT FOUND[/TD]
[TD="bgcolor: #FFF2CC"]NOT FOUND[/TD]
[TD="bgcolor: #FFF2CC"]0036F00003KKCCU[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFF2CC"]NOT FOUND[/TD]
[TD="bgcolor: #FFF2CC"]NOT FOUND[/TD]
[TD="bgcolor: #FFF2CC"]NOT FOUND[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFF2CC"]NOT FOUND[/TD]
[TD="bgcolor: #FFF2CC"]0036F00002wW3QR[/TD]
[TD="bgcolor: #FFF2CC"]0036F00002wW3QR[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFF2CC"]0036F000036skXP[/TD]
[TD="bgcolor: #FFF2CC"]NOT FOUND[/TD]
[TD="bgcolor: #FFF2CC"]NOT FOUND[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFF2CC"]0036F00002FauAq[/TD]
[TD="bgcolor: #FFF2CC"]0036F00002FauAq[/TD]
[TD="bgcolor: #FFF2CC"]0036F00002FauAq[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFF2CC"]0036F00002FauAq[/TD]
[TD="bgcolor: #FFF2CC"]0036F00002FauAq[/TD]
[TD="bgcolor: #FFF2CC"]0036F00002FauAq[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFF2CC"]0036F00003GdlKu[/TD]
[TD="bgcolor: #FFF2CC"]0036F00003Gcm5Z[/TD]
[TD="bgcolor: #FFF2CC"]0036F00003Gcm5Z[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFF2CC"]0036F00003GdlKu[/TD]
[TD="bgcolor: #FFF2CC"]0036F00003Gcm5Z[/TD]
[TD="bgcolor: #FFF2CC"]0036F00003Gcm5Z[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFF2CC"]0036F00003GdlKu[/TD]
[TD="bgcolor: #FFF2CC"]0036F00003Gcm5Z[/TD]
[TD="bgcolor: #FFF2CC"]0036F00003Gcm5Z[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFF2CC"]NOT FOUND[/TD]
[TD="bgcolor: #FFF2CC"]NOT FOUND[/TD]
[TD="bgcolor: #FFF2CC"]NOT FOUND[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFF2CC"]NOT FOUND[/TD]
[TD="bgcolor: #FFF2CC"]0036F00003AJI3r[/TD]
[TD="bgcolor: #FFF2CC"]0036F00003AJI3r[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFF2CC"]NOT FOUND[/TD]
[TD="bgcolor: #FFF2CC"]0036F00003AJI3r[/TD]
[TD="bgcolor: #FFF2CC"]0036F00003AJI3r[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFF2CC"]NOT FOUND[/TD]
[TD="bgcolor: #FFF2CC"]0036F00003AJI3r[/TD]
[TD="bgcolor: #FFF2CC"]0036F00003AJI3r[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFF2CC"]NOT FOUND[/TD]
[TD="bgcolor: #FFF2CC"]0036F00001vygDz[/TD]
[TD="bgcolor: #FFF2CC"]0036F00001vygDz[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFF2CC"]NOT FOUND[/TD]
[TD="bgcolor: #FFF2CC"]0036F00001vygDz[/TD]
[TD="bgcolor: #FFF2CC"]0036F00001vygDz[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFF2CC"]NOT FOUND[/TD]
[TD="bgcolor: #FFF2CC"]NOT FOUND[/TD]
[TD="bgcolor: #FFF2CC"]0036F000034F4Fm[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFF2CC"]NOT FOUND[/TD]
[TD="bgcolor: #FFF2CC"]NOT FOUND[/TD]
[TD="bgcolor: #FFF2CC"]0036F000034F4Fm[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFF2CC"]NOT FOUND[/TD]
[TD="bgcolor: #FFF2CC"]NOT FOUND[/TD]
[TD="bgcolor: #FFF2CC"]0036F000034F4Fm[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFF2CC"]NOT FOUND[/TD]
[TD="bgcolor: #FFF2CC"]NOT FOUND[/TD]
[TD="bgcolor: #FFF2CC"]0036F00003KKCCU[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFF2CC"]NOT FOUND[/TD]
[TD="bgcolor: #FFF2CC"]NOT FOUND[/TD]
[TD="bgcolor: #FFF2CC"]0036F00003KKCCU[/TD]
[/TR]
</tbody>[/TABLE]

I should have an output that has the unique UID records and a total count.

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 161px"><col width="100"></colgroup><tbody>[TR]
[TD="bgcolor: #D9D9D9"]Unique Record[/TD]
[TD="bgcolor: #666666"]COUNTA[/TD]
[/TR]
[TR]
[TD="bgcolor: #F3F3F3"]0036F00001pBJXO[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: #F3F3F3"]0036F00001pi66U[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: #F3F3F3"]0036F00001qh895[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: #F3F3F3"]0036F00001rCzpf[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: #F3F3F3"]0036F00001rZmIN[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: #F3F3F3"]0036F00001rZmY4[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: #F3F3F3"]0036F00001skFEx[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: #F3F3F3"]0036F00001skOB5[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: #F3F3F3"]0036F00001tDV25[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: #F3F3F3"]0036F00001tfjcl[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: #F3F3F3"]0036F00001uhvkY[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: #F3F3F3"]0036F00001uhzz8[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: #F3F3F3"]0036F00001uiz9f[/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for being helpful!
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Have you investigated Excel's built-in Pivot Table feature (on the Insert ribbon tab)?

I wasn't more specific because your sample data has 3 columns but your sample results only has one & I can't correlate anything. For example 0036F00001pBJXO appears in the results but not in the data.
 
Last edited:
Upvote 0
is that what you want?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]NAME[/td][td=bgcolor:#5B9BD5]PHONE[/td][td=bgcolor:#5B9BD5]EMAIL[/td][td][/td][td=bgcolor:#70AD47]Column1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]NOT FOUND[/td][td=bgcolor:#DDEBF7]0036F00003EAyMl[/td][td=bgcolor:#DDEBF7]0036F00003EAyMl[/td][td][/td][td=bgcolor:#E2EFDA]0036F000036skXP[/td][/tr]

[tr=bgcolor:#FFFFFF][td]NOT FOUND[/td][td]NOT FOUND[/td][td]0036F00003KKCCU[/td][td][/td][td]0036F00002FauAq[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]NOT FOUND[/td][td=bgcolor:#DDEBF7]NOT FOUND[/td][td=bgcolor:#DDEBF7]0036F00003KKCCU[/td][td][/td][td=bgcolor:#E2EFDA]0036F00003GdlKu[/td][/tr]

[tr=bgcolor:#FFFFFF][td]NOT FOUND[/td][td]NOT FOUND[/td][td]NOT FOUND[/td][td][/td][td]0036F00003EAyMl[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]NOT FOUND[/td][td=bgcolor:#DDEBF7]0036F00002wW3QR[/td][td=bgcolor:#DDEBF7]0036F00002wW3QR[/td][td][/td][td=bgcolor:#E2EFDA]0036F00002wW3QR[/td][/tr]

[tr=bgcolor:#FFFFFF][td]0036F000036skXP[/td][td]NOT FOUND[/td][td]NOT FOUND[/td][td][/td][td]0036F00003Gcm5Z[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]0036F00002FauAq[/td][td=bgcolor:#DDEBF7]0036F00002FauAq[/td][td=bgcolor:#DDEBF7]0036F00002FauAq[/td][td][/td][td=bgcolor:#E2EFDA]0036F00003AJI3r[/td][/tr]

[tr=bgcolor:#FFFFFF][td]0036F00002FauAq[/td][td]0036F00002FauAq[/td][td]0036F00002FauAq[/td][td][/td][td]0036F00001vygDz[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]0036F00003GdlKu[/td][td=bgcolor:#DDEBF7]0036F00003Gcm5Z[/td][td=bgcolor:#DDEBF7]0036F00003Gcm5Z[/td][td][/td][td=bgcolor:#E2EFDA]0036F00003KKCCU[/td][/tr]

[tr=bgcolor:#FFFFFF][td]0036F00003GdlKu[/td][td]0036F00003Gcm5Z[/td][td]0036F00003Gcm5Z[/td][td][/td][td]0036F000034F4Fm[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]0036F00003GdlKu[/td][td=bgcolor:#DDEBF7]0036F00003Gcm5Z[/td][td=bgcolor:#DDEBF7]0036F00003Gcm5Z[/td][td][/td][td][/td][/tr]

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]NOT FOUND[/td][td=bgcolor:#DDEBF7]0036F00003AJI3r[/td][td=bgcolor:#DDEBF7]0036F00003AJI3r[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]NOT FOUND[/td][td]0036F00003AJI3r[/td][td]0036F00003AJI3r[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]NOT FOUND[/td][td=bgcolor:#DDEBF7]0036F00003AJI3r[/td][td=bgcolor:#DDEBF7]0036F00003AJI3r[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]NOT FOUND[/td][td]0036F00001vygDz[/td][td]0036F00001vygDz[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]NOT FOUND[/td][td=bgcolor:#DDEBF7]0036F00001vygDz[/td][td=bgcolor:#DDEBF7]0036F00001vygDz[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]NOT FOUND[/td][td]NOT FOUND[/td][td]0036F000034F4Fm[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]NOT FOUND[/td][td=bgcolor:#DDEBF7]NOT FOUND[/td][td=bgcolor:#DDEBF7]0036F000034F4Fm[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]NOT FOUND[/td][td]NOT FOUND[/td][td]0036F000034F4Fm[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]NOT FOUND[/td][td=bgcolor:#DDEBF7]NOT FOUND[/td][td=bgcolor:#DDEBF7]0036F00003KKCCU[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]NOT FOUND[/td][td]NOT FOUND[/td][td]0036F00003KKCCU[/td][td][/td][td][/td][/tr]
[/table]

or

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Column1[/td][td=bgcolor:#70AD47]Count[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]0036F000036skXP[/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]0036F00002FauAq[/td][td]
6​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]0036F00003GdlKu[/td][td=bgcolor:#E2EFDA]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]0036F00003EAyMl[/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]0036F00002wW3QR[/td][td=bgcolor:#E2EFDA]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]0036F00003Gcm5Z[/td][td]
6​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]0036F00003AJI3r[/td][td=bgcolor:#E2EFDA]
6​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]0036F00001vygDz[/td][td]
4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]0036F00003KKCCU[/td][td=bgcolor:#E2EFDA]
4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]0036F000034F4Fm[/td][td]
3​
[/td][/tr]
[/table]
 
Last edited:
Upvote 0
Ideally I would like it to be:


[TABLE="class: head"]
<tbody>[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] ]NAME[/COLOR][/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]PHONE[/COLOR][/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]EMAIL[/COLOR][/TD]
[TD][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column1[/COLOR][/TD]
[TD="bgcolor: #70AD47"][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]NOT FOUND[/TD]
[TD="bgcolor: #DDEBF7"]0036F00003EAyMl[/TD]
[TD="bgcolor: #DDEBF7"]0036F00003EAyMl[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]0036F000036skXP[/TD]
[TD="bgcolor: #E2EFDA"]1[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]NOT FOUND[/TD]
[TD]NOT FOUND[/TD]
[TD]0036F00003KKCCU[/TD]
[TD][/TD]
[TD]0036F00002FauAq[/TD]
[TD]5[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]NOT FOUND[/TD]
[TD="bgcolor: #DDEBF7"]NOT FOUND[/TD]
[TD="bgcolor: #DDEBF7"]0036F00003KKCCU[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]0036F00003GdlKu[/TD]
[TD="bgcolor: #E2EFDA"]6[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]NOT FOUND[/TD]
[TD]NOT FOUND[/TD]
[TD]NOT FOUND[/TD]
[TD][/TD]
[TD]0036F00003EAyMl[/TD]
[TD]5[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]NOT FOUND[/TD]
[TD="bgcolor: #DDEBF7"]0036F00002wW3QR[/TD]
[TD="bgcolor: #DDEBF7"]0036F00002wW3QR[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]0036F00002wW3QR[/TD]
[TD="bgcolor: #E2EFDA"]2[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]0036F000036skXP[/TD]
[TD]NOT FOUND[/TD]
[TD]NOT FOUND[/TD]
[TD][/TD]
[TD]0036F00003Gcm5Z[/TD]
[TD]3[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]0036F00002FauAq[/TD]
[TD="bgcolor: #DDEBF7"]0036F00002FauAq[/TD]
[TD="bgcolor: #DDEBF7"]0036F00002FauAq[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]0036F00003AJI3r[/TD]
[TD="bgcolor: #E2EFDA"]3[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]0036F00002FauAq[/TD]
[TD]0036F00002FauAq[/TD]
[TD]0036F00002FauAq[/TD]
[TD][/TD]
[TD]0036F00001vygDz[/TD]
[TD]3[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]0036F00003GdlKu[/TD]
[TD="bgcolor: #DDEBF7"]0036F00003Gcm5Z[/TD]
[TD="bgcolor: #DDEBF7"]0036F00003Gcm5Z[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]0036F00003KKCCU[/TD]
[TD="bgcolor: #E2EFDA"]1[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]0036F00003GdlKu[/TD]
[TD]0036F00003Gcm5Z[/TD]
[TD]0036F00003Gcm5Z[/TD]
[TD][/TD]
[TD]0036F000034F4Fm[/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"]0036F00003GdlKu[/TD]
[TD="bgcolor: #DDEBF7"]0036F00003Gcm5Z[/TD]
[TD="bgcolor: #DDEBF7"]0036F00003Gcm5Z[/TD]
[TD][/TD]
[TD]etc..[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]NOT FOUND[/TD]
[TD]NOT FOUND[/TD]
[TD]NOT FOUND[/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"]NOT FOUND[/TD]
[TD="bgcolor: #DDEBF7"]0036F00003AJI3r[/TD]
[TD="bgcolor: #DDEBF7"]0036F00003AJI3r[/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]NOT FOUND[/TD]
[TD]0036F00003AJI3r[/TD]
[TD]0036F00003AJI3r[/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"]NOT FOUND[/TD]
[TD="bgcolor: #DDEBF7"]0036F00003AJI3r[/TD]
[TD="bgcolor: #DDEBF7"]0036F00003AJI3r[/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]NOT FOUND[/TD]
[TD]0036F00001vygDz[/TD]
[TD]0036F00001vygDz[/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"]NOT FOUND[/TD]
[TD="bgcolor: #DDEBF7"]0036F00001vygDz[/TD]
[TD="bgcolor: #DDEBF7"]0036F00001vygDz[/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]NOT FOUND[/TD]
[TD]NOT FOUND[/TD]
[TD]0036F000034F4Fm[/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"]NOT FOUND[/TD]
[TD="bgcolor: #DDEBF7"]NOT FOUND[/TD]
[TD="bgcolor: #DDEBF7"]0036F000034F4Fm[/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]NOT FOUND[/TD]
[TD]NOT FOUND[/TD]
[TD]0036F000034F4Fm[/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"]NOT FOUND[/TD]
[TD="bgcolor: #DDEBF7"]NOT FOUND[/TD]
[TD="bgcolor: #DDEBF7"]0036F00003KKCCU[/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]NOT FOUND[/TD]
[TD]NOT FOUND[/TD]
[TD]0036F00003KKCCU[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]







****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="class: head"]
<tbody>[TR]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]NAME[/COLOR][/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]PHONE[/COLOR][/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]EMAIL[/COLOR][/TD]
[TD][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column1[/COLOR][/TD]
[/TR]
[TR]
[TD="bgcolor: #DDEBF7"]NOT FOUND[/TD]
[TD="bgcolor: #DDEBF7"]0036F00003EAyMl[/TD]
[TD="bgcolor: #DDEBF7"]0036F00003EAyMl[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]0036F000036skXP[/TD]
[/TR]
[TR]
[TD]NOT FOUND[/TD]
[TD]NOT FOUND[/TD]
[TD]0036F00003KKCCU[/TD]
[TD][/TD]
[TD]0036F00002FauAq[/TD]
[/TR]
[TR]
[TD="bgcolor: #DDEBF7"]NOT FOUND[/TD]
[TD="bgcolor: #DDEBF7"]NOT FOUND[/TD]
[TD="bgcolor: #DDEBF7"]0036F00003KKCCU[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]0036F00003GdlKu[/TD]
[/TR]
[TR]
[TD]NOT FOUND[/TD]
[TD]NOT FOUND[/TD]
[TD]NOT FOUND[/TD]
[TD][/TD]
[TD]0036F00003EAyMl[/TD]
[/TR]
[TR]
[TD="bgcolor: #DDEBF7"]NOT FOUND[/TD]
[TD="bgcolor: #DDEBF7"]0036F00002wW3QR[/TD]
[TD="bgcolor: #DDEBF7"]0036F00002wW3QR[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]0036F00002wW3QR[/TD]
[/TR]
[TR]
[TD]0036F000036skXP[/TD]
[TD]NOT FOUND[/TD]
[TD]NOT FOUND[/TD]
[TD][/TD]
[TD]0036F00003Gcm5Z[/TD]
[/TR]
[TR]
[TD="bgcolor: #DDEBF7"]0036F00002FauAq[/TD]
[TD="bgcolor: #DDEBF7"]0036F00002FauAq[/TD]
[TD="bgcolor: #DDEBF7"]0036F00002FauAq[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]0036F00003AJI3r[/TD]
[/TR]
[TR]
[TD]0036F00002FauAq[/TD]
[TD]0036F00002FauAq[/TD]
[TD]0036F00002FauAq[/TD]
[TD][/TD]
[TD]0036F00001vygDz[/TD]
[/TR]
[TR]
[TD="bgcolor: #DDEBF7"]0036F00003GdlKu[/TD]
[TD="bgcolor: #DDEBF7"]0036F00003Gcm5Z[/TD]
[TD="bgcolor: #DDEBF7"]0036F00003Gcm5Z[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]0036F00003KKCCU[/TD]
[/TR]
[TR]
[TD]0036F00003GdlKu[/TD]
[TD]0036F00003Gcm5Z[/TD]
[TD]0036F00003Gcm5Z[/TD]
[TD][/TD]
[TD]0036F000034F4Fm[/TD]
[/TR]
[TR]
[TD="bgcolor: #DDEBF7"]0036F00003GdlKu[/TD]
[TD="bgcolor: #DDEBF7"]0036F00003Gcm5Z[/TD]
[TD="bgcolor: #DDEBF7"]0036F00003Gcm5Z[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NOT FOUND[/TD]
[TD]NOT FOUND[/TD]
[TD]NOT FOUND[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DDEBF7"]NOT FOUND[/TD]
[TD="bgcolor: #DDEBF7"]0036F00003AJI3r[/TD]
[TD="bgcolor: #DDEBF7"]0036F00003AJI3r[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NOT FOUND[/TD]
[TD]0036F00003AJI3r[/TD]
[TD]0036F00003AJI3r[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DDEBF7"]NOT FOUND[/TD]
[TD="bgcolor: #DDEBF7"]0036F00003AJI3r[/TD]
[TD="bgcolor: #DDEBF7"]0036F00003AJI3r[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NOT FOUND[/TD]
[TD]0036F00001vygDz[/TD]
[TD]0036F00001vygDz[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DDEBF7"]NOT FOUND[/TD]
[TD="bgcolor: #DDEBF7"]0036F00001vygDz[/TD]
[TD="bgcolor: #DDEBF7"]0036F00001vygDz[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NOT FOUND[/TD]
[TD]NOT FOUND[/TD]
[TD]0036F000034F4Fm[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DDEBF7"]NOT FOUND[/TD]
[TD="bgcolor: #DDEBF7"]NOT FOUND[/TD]
[TD="bgcolor: #DDEBF7"]0036F000034F4Fm[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NOT FOUND[/TD]
[TD]NOT FOUND[/TD]
[TD]0036F000034F4Fm[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DDEBF7"]NOT FOUND[/TD]
[TD="bgcolor: #DDEBF7"]NOT FOUND[/TD]
[TD="bgcolor: #DDEBF7"]0036F00003KKCCU[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NOT FOUND[/TD]
[TD]NOT FOUND[/TD]
[TD]0036F00003KKCCU[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

</body>
 
Upvote 0
Sorry if im not explaining it correct.

I am trying to count and show the totals for each unique SKU

I have tried pivot tables, but cannot work it out.
 
Upvote 0
so use Power Query and M-code

Code: 
[SIZE=1]// Table1
let
    T2C = Table.ToColumns(Excel.CurrentWorkbook(){[Name="Table1"]}[Content]),
    C2T = Table.FromList(T2C, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Expand = Table.ExpandListColumn(C2T, "Column1"),
    Filter = Table.SelectRows(Expand, each ([Column1] <> "NOT FOUND")),
    Group = Table.Group(Filter, {"Column1"}, {{"Count", each Table.RowCount(_), type number}})
in
    Group[/SIZE]

and you'll get the second green table result
 
Upvote 0
Two other approaches.
Formulas as shown copied down or this macro. Macro results shown in columns J:K

Rich (BB code): 
Sub UniqueAndCount()
  Dim d As Object
  Dim a As Variant, itm As Variant
  
  Set d = CreateObject("Scripting.Dictionary")
  a = Range("A2", Range("C" & Rows.Count).End(xlUp)).Value
  For Each itm In a
    If itm <> "NOT FOUND" Then d(itm) = d(itm) + 1
  Next itm
  Range("J2:K2").Resize(d.Count) = Application.Transpose(Array(d.Keys, d.Items))
  Range("J1:K1").Value = Array("List", "Count")
End Sub

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:130px;" /><col style="width:140px;" /><col style="width:140px;" /><col style="width:17px;" /><col style="width:140px;" /><col style="width:53px;" /><col style="width:17px;" /><col style="width:17px;" /><col style="width:17px;" /><col style="width:140px;" /><col style="width:53px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; ">NAME</td><td style="font-size:10pt; ">PHONE</td><td style="font-size:10pt; ">EMAIL</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">List</td><td style="font-size:10pt; text-align:right; ">Count</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">List</td><td style="font-size:10pt; text-align:right; ">Count</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">NOT FOUND</td><td style="font-size:10pt; ">0036F00003EAyMl</td><td style="font-size:10pt; ">0036F00003EAyMl</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">0036F00003GdlKu</td><td style="font-size:10pt; text-align:right; ">3</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">0036F000036skXP</td><td style="font-size:10pt; text-align:right; ">1</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; ">NOT FOUND</td><td style="font-size:10pt; ">NOT FOUND</td><td style="font-size:10pt; ">0036F00003KKCCU</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">0036F00002FauAq</td><td style="font-size:10pt; text-align:right; ">6</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">0036F00002FauAq</td><td style="font-size:10pt; text-align:right; ">6</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; ">NOT FOUND</td><td style="font-size:10pt; ">NOT FOUND</td><td style="font-size:10pt; ">0036F00003KKCCU</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">0036F000036skXP</td><td style="font-size:10pt; text-align:right; ">1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">0036F00003GdlKu</td><td style="font-size:10pt; text-align:right; ">3</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; ">NOT FOUND</td><td style="font-size:10pt; ">NOT FOUND</td><td style="font-size:10pt; ">NOT FOUND</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">0036F00001vygDz</td><td style="font-size:10pt; text-align:right; ">4</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">0036F00003EAyMl</td><td style="font-size:10pt; text-align:right; ">2</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; ">NOT FOUND</td><td style="font-size:10pt; ">0036F00002wW3QR</td><td style="font-size:10pt; ">0036F00002wW3QR</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">0036F00003AJI3r</td><td style="font-size:10pt; text-align:right; ">6</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">0036F00002wW3QR</td><td style="font-size:10pt; text-align:right; ">2</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; ">0036F000036skXP</td><td style="font-size:10pt; ">NOT FOUND</td><td style="font-size:10pt; ">NOT FOUND</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">0036F00003Gcm5Z</td><td style="font-size:10pt; text-align:right; ">6</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">0036F00003Gcm5Z</td><td style="font-size:10pt; text-align:right; ">6</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; ">0036F00002FauAq</td><td style="font-size:10pt; ">0036F00002FauAq</td><td style="font-size:10pt; ">0036F00002FauAq</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">0036F00002wW3QR</td><td style="font-size:10pt; text-align:right; ">2</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">0036F00003AJI3r</td><td style="font-size:10pt; text-align:right; ">6</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:10pt; ">0036F00002FauAq</td><td style="font-size:10pt; ">0036F00002FauAq</td><td style="font-size:10pt; ">0036F00002FauAq</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">0036F00003EAyMl</td><td style="font-size:10pt; text-align:right; ">2</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">0036F00001vygDz</td><td style="font-size:10pt; text-align:right; ">4</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:10pt; ">0036F00003GdlKu</td><td style="font-size:10pt; ">0036F00003Gcm5Z</td><td style="font-size:10pt; ">0036F00003Gcm5Z</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">0036F00003KKCCU</td><td style="font-size:10pt; text-align:right; ">4</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">0036F00003KKCCU</td><td style="font-size:10pt; text-align:right; ">4</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:10pt; ">0036F00003GdlKu</td><td style="font-size:10pt; ">0036F00003Gcm5Z</td><td style="font-size:10pt; ">0036F00003Gcm5Z</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">0036F000034F4Fm</td><td style="font-size:10pt; text-align:right; ">3</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">0036F000034F4Fm</td><td style="font-size:10pt; text-align:right; ">3</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:10pt; ">0036F00003GdlKu</td><td style="font-size:10pt; ">0036F00003Gcm5Z</td><td style="font-size:10pt; ">0036F00003Gcm5Z</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-size:10pt; ">NOT FOUND</td><td style="font-size:10pt; ">NOT FOUND</td><td style="font-size:10pt; ">NOT FOUND</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-size:10pt; ">NOT FOUND</td><td style="font-size:10pt; ">0036F00003AJI3r</td><td style="font-size:10pt; ">0036F00003AJI3r</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="font-size:10pt; ">NOT FOUND</td><td style="font-size:10pt; ">0036F00003AJI3r</td><td style="font-size:10pt; ">0036F00003AJI3r</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="font-size:10pt; ">NOT FOUND</td><td style="font-size:10pt; ">0036F00003AJI3r</td><td style="font-size:10pt; ">0036F00003AJI3r</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="font-size:10pt; ">NOT FOUND</td><td style="font-size:10pt; ">0036F00001vygDz</td><td style="font-size:10pt; ">0036F00001vygDz</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="font-size:10pt; ">NOT FOUND</td><td style="font-size:10pt; ">0036F00001vygDz</td><td style="font-size:10pt; ">0036F00001vygDz</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="font-size:10pt; ">NOT FOUND</td><td style="font-size:10pt; ">NOT FOUND</td><td style="font-size:10pt; ">0036F000034F4Fm</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="font-size:10pt; ">NOT FOUND</td><td style="font-size:10pt; ">NOT FOUND</td><td style="font-size:10pt; ">0036F000034F4Fm</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="font-size:10pt; ">NOT FOUND</td><td style="font-size:10pt; ">NOT FOUND</td><td style="font-size:10pt; ">0036F000034F4Fm</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td style="font-size:10pt; ">NOT FOUND</td><td style="font-size:10pt; ">NOT FOUND</td><td style="font-size:10pt; ">0036F00003KKCCU</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td style="font-size:10pt; ">NOT FOUND</td><td style="font-size:10pt; ">NOT FOUND</td><td style="font-size:10pt; ">0036F00003KKCCU</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E2</td><td >=IFERROR(IFERROR<span style=' color:008000; '>(LOOKUP<span style=' color:#0000ff; '>(2, 1/<span style=' color:#ff0000; '>(COUNTIF<span style=' color:#804000; '>($E$1:E1,$A$2:$A$23)</span>=0)</span>/<span style=' color:#ff0000; '>(A$2:A$23<>"NOT FOUND")</span>, $A$2:$A$23)</span>,IFERROR<span style=' color:#0000ff; '>(LOOKUP<span style=' color:#ff0000; '>(2, 1/<span style=' color:#804000; '>(COUNTIF<span style=' color:#ff7837; '>($E$1:E1, $B$2:$B$23)</span>=0)</span>/<span style=' color:#804000; '>(B$2:B$23<>"NOT FOUND")</span>, $B$2:$B$23)</span>,LOOKUP<span style=' color:#ff0000; '>(2, 1/<span style=' color:#804000; '>(COUNTIF<span style=' color:#ff7837; '>($E$1:E1, $C$2:$C$23)</span>=0)</span>/<span style=' color:#804000; '>(C$2:C$23<>"NOT FOUND")</span>, $C$2:$C$23)</span>)</span>)</span>,"")</td></tr><tr><td >F2</td><td >=IF(E2="","",COUNTIF<span style=' color:008000; '>(A$2:C$23,E2)</span>)</td></tr></table></td></tr></table>
 
Upvote 0
Thank you. FYI, I did get this to work using spreadsheet formulas in Ecel. (I couldnt get it to work in google sheets)

Thanks Again!
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Please help!! Need help with specialized formula.
Replies
3
Views
972
Eric W
Eric W
S
  • Question Question
Search a table and return rows based on criteria of 2 cells
Replies
1
Views
723
Fluff
Fluff
H
  • Question Question
comparing values in a column against multiple sheets and returning subsequent values from other columns
Replies
1
Views
1K
DanteAmor
DanteAmor
B
  • Question Question
Count unique cells after multi-cell matching criteria against a key cell
Replies
8
Views
1K
James006
James006
R
  • Question Question
How to find last 12 nonzero values in a column of data
Replies
5
Views
840
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,823
Messages
6,181,179
Members
453,021
Latest member
Justyna P

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