3,890 questions
ooops wrong spot.
*How would I write the formula to automatically pull the name of the items from the box of information without getting blanks or duplicates?
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(220.8, 61%, 31%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJ%3C/text%3E%3C/svg%3E)
JayDee
0
Reputation points
Hello, I'm trying to create a list of Names and the Number of times they appear from a box of information similar to the picture.
Current data range is B2:AT21
Below the data range I was in putting the Formula
How would I write the formula to automatically pull the name of the items from the box of information without getting blanks or duplicates?** (that way it will auto populate a list)
I inputted in the column next to it "=Countif(B2:AT21;A24)" and reference the cell next to the left as the unique id to get the count.
Thanks in advance
Box of information:
Main List w/quantity
Microsoft 365 and Office | Excel | For business | Windows
{count} votes
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(172.8, 53%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBS%3C/text%3E%3C/svg%3E)
Barry Schwarz 3,746 Reputation points2024-04-28T17:47:04.45+00:00 I don't think you can do this with a formula but it would be easy to do with a macro. Write back if you are willing to consider that solution.
-
JayDee 0 Reputation points
2024-04-28T18:08:42.2233333+00:00 Barry Schwarz sure.
I was thinking something to compile each column into a single column and then remove duplicates but I've never done a macro and have already spent hours googling and trying solutions. Everything I've found seems to let me do what I'm looing for if all the names were in one long column and not spread out across a field.
-
Barry Schwarz 3,746 Reputation points
2024-04-29T01:02:28.51+00:00 Google excel vba tutorial and take your pick. I found the ones from learnit useful.
Sign in to comment
2 answers
Sort by: Most helpful
-
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(147.20000000000002, 13%, 24%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJH%3C/text%3E%3C/svg%3E)
Jiajing Hua 18,060 Reputation points Moderator2024-04-29T06:32:10.6466667+00:00 Hi @JayDee
I suggest you try to use Tocol and Unique functions.
If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
-
Jiajing Hua 18,060 Reputation points Moderator
2024-05-02T02:10:48.62+00:00 Hi @JayDee
I am checking this issue, if you have a chance to be back, please whether my information is helpful.
Sign in to comment -