Sort columns in an array so each column has either a blank or a duplicate value

[Kinkzor]

Hi all,

Little hard to explain, I Will add an image to show the sort of array I am working on:

So each column contains omly the duplicate values. Each row will contain the letters correlating to each number, with blanks if that number has no link to a specific letter.

 

[VBA Geek]

How to identify what should go in each column? In your case it's letters from A to D so it can be done as per below. But how is it in your real scenario?



<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B9</th><td style="text-align:left">=IF(<font color="Blue">ISNUMBER(<font color="Red">MATCH(<font color="Green">CHAR(<font color="Purple">MOD(<font color="Teal">COLUMNS(<font color="#FF00FF">$B:B</font>)-1,26</font>)+65</font>),$B1:$E1,0</font>)</font>),CHAR(<font color="Red">MOD(<font color="Green">COLUMNS(<font color="Purple">$B:B</font>)-1,26</font>)+65</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />

Excel 2010
	A	B	C	D	E
1	1	A	D	C	B
2	2	C		B	D
3	3			A
4	4	C	A
5	5		D		A
6
7
8
9	1	A	B	C	D
10	2		B	C	D
11	3	A
12	4	A		C
13	5	A			D
Sheet17

 

[Kinkzor]

Hi and thanks for the reply.

Each row is a department, the columns are filled with application names used in each department. Some departments have 1 or 2 apps used some have 50.

App names are typical such as office 2016 or vba dev and so on.

 

[VBA Geek]

so I guess you will need to make a mapping of the apps that would need to go in each column and then update the formula accordingly. remove the part where it creates the letter name CHAR(MOD(COLUMNS($B:B)-1,26)+65) and replace it with the app name

 

[Kinkzor]

so I guess you will need to make a mapping of the apps that would need to go in each column and then update the formula accordingly. remove the part where it creates the letter name CHAR(MOD(COLUMNS($B:B)-1,26)+65) and replace it with the app name

Hi VBA Geek, thanks for the reply.

The issue is i have in the real sheet around 900 different application names and 300+ departments (we are a large company).

I could take all apps, remove duplicates, make this new list the top row and then tell excel to compare an entire row with the value at the top row, and if it finds it.. print a "Yes".

This way I would eventually have a top row of app names, left column of departments, with a "Yes" in each square where the app is used.

Make sense? This should work, I will give it a shot now, thanks!