Condense list into adjacent column?

[lkconcepts]

I am looking to create a spreadsheet to analyze another formatted spreadsheet. There is a list of names and I need to count how many times those names appear. I am arranging formulas on sheet 1 to draw information from sheet 2, so that if I paste a database from Oracle onto A1 of sheet 2 then sheet 1 will populate all of the metrics I need. The point I am having difficulty is in condensing a list of names so that I can automate the counting.

Column A is 40,000 names, which I pulled from sheet 2 with a TRIM function and extended the formula down 40,000 cells. I did this because our database has trailing spaces. I would like column B to be a formula to pull one of each instance of the name. It is easy to do manually with an advanced filter, but I am hoping there is a formula that can be used to the same effect, so I don't have to teach other people how to use advanced filter every time we analyze these databases. This would allow me to set up column C as a COUNTIF, taking the name from the condensed list in column B.

It should look something like this:

Raw Names	Condensed Names	Qty
A	A	4
A	B	3
A	C	1
A
B
B
B
C

<tbody>
</tbody>

Thank you!

 

[Joe4]

Welcome to the Board!

Why not just copy the column over, and then use the built-in "Remove Duplicates" functionality to get the unique listing of names, and then do the COUNTIF on those?
If you are worried about people not being to do that, you can use Macros/VBA to automate it.

 

[DRSteele]

Welcome to the forum.

If you have the most recent version of Excel, the new function UNIQUE will make quick work of extracting a distinct list of items like those in the Raw Name column. The function COUNTIFS can then produce the Qty column. See here for a vid about UNIQUE: [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]https://www.youtube.com/watch?v=vy8y4CN-IHY&t=32s

You can also use a Pivot Table to create the distinct list (and to create the Qty column).
[/FONT]

 

[sandy666]

If you've PowerQuery aka Get&Transform you can try:

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Condensed = Table.RenameColumns(Table.Group(Source, {"Raw Names"}, {{"Qty", each Table.RowCount(_), type number}}),{{"Raw Names", "Condensed"}})
in
    Condensed[/SIZE]

Raw Names		Condensed	Qty
A		A	4​
A		B	3​
A		C	1​
A
B
B
B
C

After any changes in Col.A use Ctrl+Alt+F5 to update condesed table

 

[lkconcepts]

Thank you all very much, this information is perfect! I'm going to play around with the VBA and Get&Transform methods for now. The UNIQUE command is absolutely perfect but my organization hasn't updated to the latest version quite yet.

 

[sandy666]

You are welcome

Have a nice day