Excel - Column Information Moved to Row Information 2

[useractive]

Lets's say I have a list of data listed like this starting in Column A, Row 2, then Row 3, Row 4, etc. all in Column A

1
2
3
3
3
4
4
5
6
6

Now I want to Have a formula in Column B, First row that looks at Column A and pulls the Number from there (1 for example). Then I copy over the formula to Column C, Row 1 so it looks at the next number down the Column (2). I want this to continue along across Row 1, for every column I put the forumla into it. The catch to all this (if it's not already difficult) I don't want it to duplicate numbers.

I'd like it too look like this.
1 2 3 4 5 6
1
2
3
3
3
4
4
5
6
6

Is this possible? Or would I need to break out the information first from Column A (If statement and such so that it shows a 1 first, and then a 0 for each duplicate of that value). Any help would be greatly appreciated.

Thanks,
Swish

 

[DaleWatson123321]

Hi Swish,

One option would be to use a "unique" extraction of the data from column A.

This would be extracted to a separate sheet, and "transposed" from there copied back to B1. Transposing it will cause the data to change from "vertical" orientation to "horizontal".

If you can confirm that this is what you want, I can quickly create a model to do this, and email it to you.

Just email me first, and I'll send the file via return email.

Regards, ...Dale Watson

WORK: dwatson@bsi.gov.mb.ca
HOME: nd.watson@shaw.ca

 

[useractive]

HI Dale,

That is kind of what I want, but would there be a way to delete the duplicate items without going in manually?

If we did just a transposition of the data, then we would see 1 2 3 3 3 4 4 5 6 6 across the top. I'm looking for a way at the same time, or another step afterwards to get rid of the duplicated items. I'm looking at about 1500 columns of numbers.

Thanks,
Swish

 

[DaleWatson123321]

Hi Swish,

Perhaps I should have been more clear about the "unique" extraction. This implicitly means that any "duplicates" will be eliminated during the unique extraction.

Where you refer to "1500 columns of numbers", I expect this HAS to mean "rows", as Excel only has a maximum of 256 columns.

In reference to the 256 column limit, this of course means that IF you want each of the values in its own cell, then there will be a problem if there are MORE than 256 "unique" numbers - i.e. when the numbers are transposed.

If there ARE more than 256 unique numbers, then other options will need to be considered, such as:
a) transposing to more than one row, or perhaps
b) concatenation of the numbers, separated by commas.

I suggest "seeing is believing", so once I email you the file, you'll become a "believer". ;-)

Regards, ...Dale Watson

WORK: dwatson@bsi.gov.mb.ca
HOME: nd.watson@shaw.ca

 

[GlennUK]

If you use these formulae, it will do what you want ( within the limits of Excel, which, as pointed out by Dale Watson means 256 columns of data only ).

Type =A2 into cell B1

Then in cell C1 type
=INDEX($A$2:$A$31,MAX(IF(B1=$A$2:$A$31,ROW($A$2:$A$31),0)))

except use 1500 ( or whatever ) instead of the 31 I have used, for the number of rows to search, and press CTRL-SHIFT-ENTER instead of ENTER to complete the entry.

Now copy cell C1 across D1 to whatever column you require, and you should get your unique numbers.

This will only work if the numbers in the list are in ascending order.

Glenn.

 

[useractive]

Thanks Dale and Glenn. Both of your ideas and formulas helped out perfectly. I am able to do exactly what I needed to do now.

Thanks again. I appreciate all the help.

Swish