Complex transpose problem with simplifying records

[ang_smiles]

Hello! I have a complex issue I'm trying to resolve. I know it's going to use transpose, but I also need to simplify my data...

I'd like to turn something like this (there are three different samples, and they each have the same five loci. The alleles may or may not be different):

Sample  Locus  Alleles
a       1         AA
a       2         B
a       3         C
a       4          DD
a       5         EEE
b       1          F
b       2         GG
b       3          H
b       4          I
b       5          JJ
c       1          K
c       2          LLL
c       3          MM
c       4          N
c       5          OO

into this (note, I don't need to copy the headers, just the data):

Sample     1         2         3         4         5
a         AA       B         C         DD      EEE
b        F          GG      H           I         JJ
c          K        LLL      MM       N         OO

I've been searching for hours on google and on various forums and threads, but I the only thing I could find was kind of the "opposite" of my situation (i.e. going from B -> A rather than I want A -> B)

 

[oldchippy]

Hi ang_smiles,

Welcome to the forum, please take time out to read the rules of the forum if you haven't already, you can find them here

http://chandoo.org/forum/threads/new-users-please-read.294/

With regard to your question here one way to transpose your data with a little help as it's not straightforward in a single formula

Insert helper column in original data at column C and in C2 insert

=A2&B2 and copy down

Over in say column G:L, insert

Sample, 1,2,3,4,5,

Then in G2:G4, insert

a,b,c

Then in H2 insert the following formula and copy across and down

=VLOOKUP($G2&H$1,$C$2:$D$16,2,TRUE)

 

[NARAYANK991]

Hi Ang ,

Another option is here.

Narayan

 

[ang_smiles]

Thanks, I think both of those will work. Now to work on deciphering them to see which will work better! Thanks so much.

 

[oldchippy]

Thanks for the feedback ang_smiles, you've got two options now so best of luck

 

[ang_smiles]

Hmm okay. I think the VLOOKUP response from oldchippy will work more easily with my data (these data sets can get really large... the # of samples can be up to 48, each with 16 loci... okay it's not "that large" of a data set compared to some...)

I don't mind having to write my headers for the loci across the top in H1:L1 (those will always stay constant)... but is there a formula that I can use to populate the sample name for each row? Since the data is being exported from another program, and the sample names will always change, it would be too much of a headache to manually copy each sample name each time I want to transpose the data.

So, from the VLOOKUP response, where in G2:G4 i would enter a,b,c, is there a formula that could take the sample names from column A and put them in G2:G4? Obviously this would then create a circular reference for the VLOOKUP formula?

In other words, since each sample name will be repeated down column A 16 times (once for each locus), is there a formula or something that I can do in column G such that G2=A2, G3=A2+16, G4=A2+32, etc?

Thanks again for all your help. I went to the library last night and studied some Excel books, but as I'm sure we all know, the books only take us so far and we usually have questions about what the books don't tell us...

(the easiest solution of all for me would be a Pivot Table, but since the data can't be text (from what I've been able to learn), it defeats that for me )

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[ang_smiles]

Thanks so much for both of your responses.... I've been playing around with them for the last week or so, and I think I've got all the kinks worked out. What I ended up doing for the "row headers" (sample names) in the transposed column was to use the advanced filter to copy "unique records only" to the second sheet, and then I was able to use the INDEX(MATCH) function that Narayan suggested.

Lots of trial and error on my part, but I think I got it! Thanks a bunch