Multiple dynamic lists in columns - move data to same row and count

[john69]

Hello all, I have dynamic data (may have only two columns or may have 10 columns and number of rows will most always be different) where I some of the data is the same and some not. What I am trying to accomplish is move the like codes to the same row and get a total count of each. I have a sample input (source) and what I want it to look like (SORTED & ALIGNED). Hope this makes sense.
Thank you in advance for your any help.

SOURCE				SORTED & ALIGNED
LIST 1	LIST 2	LIST 3		LIST 1	LIST 2	LIST 3	COUNT
ATH001	AST002	AST002			AST002	AST002	2
ATH002	ATH001	ATH001		ATH001	ATH001	ATH001	3
ATH003	ATH002	ATH002		ATH002	ATH002	ATH002	3
AWC811	AWC811	AWC811		ATH003			1
CGI020	BYH001	CGI020		AWC811	AWC811	AWC811	3
CLA001	CGI020	CLA001			BYH001		1
CRL004	CRL002	CRL002		CGI020	CGI020	CGI020	3
CWC006	CRL003	CRL003		CLA001		CLA001	2
EAS001	CWC006	CWC006			CRL002	CRL002	2
EAS002	FLE001	FLE001			CRL003	CRL003	2
FLE001				CRL004			1
				CWC006	CWC006	CWC006	3
				EAS001			1
				EAS002			1
				FLE001	FLE001	FLE001	3

<colgroup><col><col><col><col span="5"></colgroup><tbody>
</tbody>

 

[duggie33]

Are you mainly trying to determine the count of each "code"? If yes, COUNTIF should get you that quick and easy. There are lots of resources for it on the internet.

 

[john69]

Hi duggie33,

Yes and no, I also need to know which list they are in which is why I was doing it this way. I am using this (sorted) list to print and sort some documentation into any number of piles according to how many columns there are.

 

[duggie33]

Hi John,

The sorting and aligning is the part I do not see a straightforward way to achieve. VBA is probably your best bet on that.

I propose you have your source lists; in your example, three of them. Then you have a second table with first column being your list of unique “codes”, second/third/fourth being the count of each “code” in each individual list, and a fifth column which sums the second/third/fourth. It would summarize the count of each code in each list and the total for all lists.

Dynamic ranges for each list in your source data, a listing of unique codes, countif forumlas, done. Sounds easy right?

Doug