[Excel (alle versies)] Uitverdelen waardes over kolommen

[gambieter]

Volgens mij is dit Excel-versie afhankelijk, maar voor het geval dat: ik heb Excel 2003, 2007 en 2010

Ik heb een analyse van bacteriele chromosomen gedaan waarbij 19 isolaten met elkaar vergeleken zijn. De output is een tab-delimited file waarbij voor elk gen is aangegeven of er een vergelijkbaar gen is in de andere isolaten. Dat bestand is wel netjes in rijen verdeeld, maar helaas niet als een kruistabel voor de kolommen.

Als voorbeeld, elke waarde is weergegeven met een nummer (van het isolaat) en identifier van het gen. Voorbeeld:

code:

1:gen1    2:gen1    3:gen1    4:gen1    6:gen1    8:gen1
1:gen2    3:gen2    4:gen2    7:gen2    8:gen1
1:gen3    3:gen3
5:gen4    6:gen4    7:gen4    8:gen4

Nu is dit per rij dus geen probleem, maar ik wil ze ook in de juiste kolommen krijgen:

code:

Bact_1    Bact_2    Bact_3    Bact_4    Bact_5    Bact_6    Bact_7    Bact_8
1:gen1    2:gen1    3:gen1    4:gen1              6:gen1              8:gen1
1:gen2              3:gen2    4:gen2                        7:gen2    8:gen1
1:gen3              3:gen3
                                        5:gen4    6:gen4    7:gen4    8:gen4

Nu gaat het hier uiteindelijk om meer dan 6000 rijen, dus handmatig gaat het hem liever niet worden

Ik ben vast gaan zoeken op terminologie als "uitverdelen data in kolommen" en "namen splisten over kolommen", maar dat is niet wat ik zoek.

Kan ik hier een hidden cel-code toevoegen, door bijvoorbeeld alle "1:" om te zetten naar een kolom 1 code? Of is er een truc die ik heb gemist?

Edit: ik heb nu net dit gevonden: If a cell in range of cells in Excel 2010 contains specific text, move whole cell text into a different column. Dat is met een array, en ga ik met een kleine dataset proberen .

[ Voor 11% gewijzigd door gambieter op 09-11-2013 21:41 ]

[C0rnelis]

Het is mij niet helemaal duidelijk of je controle hebt over de output? Verder heeft Excel voor zover ik weet sowieso beperkte mogelijkheden met het uitlezen van niet-standaard scheidingstekens in vergelijking met bijvoorbeeld open/libre office - of die wel kunnen betekenen voor dit specifieke geval kan ik je echter niet vertellen.

Persoonlijk zou ik voor zoiets de output aanpassen of, indien dat niet mogelijk is, een scriptje schrijven om de output aan te passen naar je wensen.

nav je edit: zoiets zou inderdaad ook een mogelijkheid zijn - dan doorzoek je simpelweg je werkblad waar de kolommen in de ongewenste volgorde staan in een ander werkblad waarbij je zelf de kolommen op de juiste plek plaatst maar met formules uitvogelt óf en welke gen moet worden weergegeven.

[ Voor 23% gewijzigd door C0rnelis op 09-11-2013 21:50 ]

[Xepos]

Als je wilt kun je dit redelijk makkelijk bereiken door VBA te gebruiken.

Je kopieert eerst alles onder elkaar in een sheet of in een array al vind ik een sheet makkelijk/veiliger zodat je de originele input nog altijd kunt terugzien.

Overigens als de waardes altijd beginnen met 1, 2, 3, 4 en in de zelfde kolom komen dan kan je Left("Je woord hier", 1). Dan pakt die de 1e letter van de string. In jouw geval 1, 2, 3 ...

Dan kun je met een Select case het verdelen over de kolommen.
Als je wilt kan ik je hier wel verder mee helpen. Stuur me maar een berichtje weet niet of ik over een uur dit topic nog kan onthouden

[ Voor 14% gewijzigd door Xepos op 09-11-2013 21:54 ]

[gambieter]

C0rnelis schreef op zaterdag 09 november 2013 @ 21:47:
Het is mij niet helemaal duidelijk of je controle hebt over de output? .

Nee, ik heb daar geen controle over. Dan zou ik met de maker van de software moeten gaan praten .

Xepos schreef op zaterdag 09 november 2013 @ 21:52:
Als je wilt kun je dit redelijk makkelijk bereiken door VBA te gebruiken.

Je kopieert eerst alles onder elkaar in een sheet of in een array al vind ik een sheet makkelijk/veiliger zodat je de originele input nog altijd kunt terugzien.

Overigens als de waardes altijd beginnen met 1, 2, 3, 4 en in de zelfde kolom komen dan kan je Left("Je woord hier", 1). Dan pakt die de 1e letter van de string. In jouw geval 1, 2, 3 ...

Dan kun je met een Select case het verdelen over de kolommen.
Als je wilt kan ik je hier wel verder mee helpen. Stuur me maar een berichtje weet niet of ik over een uur dit topic nog kan onthouden

Moet ik eerst VBA gaan leren

Maar de array werkt zoals aangegeven: http://stackoverflow.com/...cific-text-move-whole-cel. Misschien niet elegant maar met:

code:

=INDEX(A2:H2,MATCH(TRUE,NOT(ISERROR(SEARCH("3:",A2:H2))),0))

en Ctrl-Shift-Enter werkt het met de kleine set. Nu eens met een echte subset proberen

[Xepos]

gambieter schreef op zaterdag 09 november 2013 @ 21:57:
[...]

Nee, ik heb daar geen controle over. Dan zou ik met de maker van de software moeten gaan praten .

[...]

Moet ik eerst VBA gaan leren

Maar de array werkt zoals aangegeven: http://stackoverflow.com/...cific-text-move-whole-cel. Misschien niet elegant maar met:

code:

=INDEX(A2:H2,MATCH(TRUE,NOT(ISERROR(SEARCH("3:",A2:H2))),0))

en Ctrl-Shift-Enter werkt het met de kleine set. Nu eens met een echte subset proberen

VBA is erg makkelijk om te leren.
Weet niet of je veel moet doen anders kan ik je wel een stukje op weg helpen.

Maar als bovenstaande formule ook gewoon werkt dan is het geen probleem

[ Voor 4% gewijzigd door Xepos op 09-11-2013 22:01 ]

[gambieter]

Dank voor het aanbod . Voorlopig ga ik dit even doen, ik ga in januari een cursus Perl volgen en dan zal VBA ook wel aangeleerd kunnen worden als ik ooit tijd heb

[gambieter]

En yep, de array werkt

[ Voor 7% gewijzigd door gambieter op 09-11-2013 22:24 ]

[C0rnelis]

Misschien leuk om je oplossing hier ook neer te zetten of staat het letterlijk op stackoverflow?

[gambieter]

Yep, het is letterlijk met de array:

code:

{=INDEX(A2:H2,MATCH(TRUE,NOT(ISERROR(SEARCH("3:",A2:H2))),0))}

en invoeren met Ctrl-Shit-Enter

[gambieter]

Hm, het enige waar ik nu nog tegenaan loop is of de array case-sensitive kan worden gemaakt. Nu pakt de array de 0:Cj en 10:cje bij een array gedefinieerd als "0:Cj".

[Lustucru]

Op het eerste gezicht is de omweg met match, true(iserror) etc juist bedoeld om de formule te laten zoeken in de hele string.

Dan kan het simpeler en beter aansluitend op wat je wilt, er vanuit gaande dat de kolom-determinant altijd vooraan staat:

code:

=INDEX($A1:$H1;VERGELIJKEN("2:*";$A1:$H1;0))

en geen matrixformule dit keer, maar simpel gebruik van een wildcard.
Eventueel die "2:*" vervangen door een verwijzing bv 'K1 & "*"' en je kunt de hele mikmak in een keer doorslepen.

edit:
En aangezien index(vergelijken) een omslachtige manier is om lookup te beschrijven is dat allemaal equivalent aan:

code:

=HORIZ.ZOEKEN(K$1 & ":*";$A1:$H1;1;0)                                   
=HLOOKUP(K$1 & ":*",$A1:$H1,1,0)                                            'angelsaksisch

[ Voor 48% gewijzigd door Lustucru op 10-11-2013 10:45 . Reden: Het is uiteindelijk best simpel. ]

[dlare]

Wat me opvalt is dat de output een vreemde waarde bevat in regel 2: "8:gen1".
Ik had hier "8:gen2" verwacht.
Het maakt nogal een verschil voor de oplossing.
Klopt het dat in regel X altijd waarden staan in vorm "Y:genX"?
En X loopt van 1 tm 6000? (gen)
En Y loopt van 1 tm 19? (idolaat)

Wat wil je weten?
In jouw data komt 1 waarde dubbel voor "8:gen1".
Wil je tellen?
En output in 19 kolommen en 6000 regels?

Simpele oplossing: "countif"
Nieuw tabblad
waarde B1 = 1
waarde C1 = 2
etc tm 19

waarde A2 = 1
waarde A3 = 2
etc tm 6000

dan in cel b2 de formule
=COUNTIF(data!$A$1:$S$6000;B$1&":gen"&$A2)

Doortrekken en klaar.
Of als je tekst wilt:
=IF(COUNTIF(data!$A$1:$S$6000;B$1&":gen"&$A2)=0;"";B$1&":gen"&$A2)

Of sla ik de plank nu volledig mis??

[ Voor 4% gewijzigd door dlare op 10-11-2013 02:16 ]

[gambieter]

dlare schreef op zondag 10 november 2013 @ 02:07:
Wat me opvalt is dat de output een vreemde waarde bevat in regel 2: "8:gen1".
Ik had hier "8:gen2" verwacht.

Ah, tikfoutje. Moet inderdaad "8:gen2" zijn, ik heb gewoon even wat fictieve data gebruikt

Klopt het dat in regel X altijd waarden staan in vorm "Y:genX"?
En X loopt van 1 tm 6000? (gen)
En Y loopt van 1 tm 19? (idolaat)

Nee, iedere dataset heeft ongeveer 1700 genen, de overlap is ongeveer 1200-1300 genen afhankelijk van welke je vergelijkt. Maar door de unieke genen is de complete dataset ongeveer 6000 genen. Dat maakt het juist interessant voor de bioloog in me

Of sla ik de plank nu volledig mis??

Ik denk het wel, maar dat zal wel door mijn uitleg en tikfoutje komen . Het gaat niet om het tellen, maar juist om het netjes uitverdelen van de waardes die in de output niet in de juiste kolommen staan

@Lustucru: ga ik mee aan de slag, dank

Reden: Het is uiteindelijk best simpel.

Voor de Excel-amateur valt dat best tegen

Edit: Lustucru, Excel 2010 vind je formules niet correct, klaagt over de $h1. Mijn probleem is dat ik niet kan troubleshooten daar ik de formule niet begrijp (*schaam*). Ook de tweede optie geeft een error, het tweede aanhalingsteken (in "2:*") is blijkbaar fout?

Op zich is het maar een klein probleempje aangaande de 0:Cj en de 10:cj, dat is makkelijk handmatig op te lossen en bij de anderen gaat het niet fout. Ik kan dat ook voorkomen door een snelle Replace waarbij ik er voor zorg dat alle identifiers uniek zijn. Niet de fraaiste oplossing, maar wel eentje die het gewenste resultaat geeft

[ Voor 25% gewijzigd door gambieter op 10-11-2013 03:04 ]

[Lustucru]

gambieter schreef op zondag 10 november 2013 @ 02:19:
Edit: Lustucru, Excel 2010 vind je formules niet correct, klaagt

Vertalen gaat in twee stappen:
1: Vervang de functienamen door hun lokale equivalent (-->horiz.zoeken() -->Hlookup())
2: vervang het parameterscheidingsteken door zijn lokale variant (;-->,)

code:

=HORIZ.ZOEKEN(K$1 & ":*";$A1:$H1;1;0)                    'NL-excel, NL landinstelling windows
=HORIZ.ZOEKEN(K$1 & ":*"|$A1:$H1|1|0)                    'NL-excel Pipe ingesteld als lijstscheidingsteken
=HLOOKUP(K$1 & ":*",$A1:$H1,1,0)                             'En-excel, UK landinstelling windows
=HLOOKUP(K$1 & ":*";$A1:$H1;1;0)                             'En-excel, NL landinstelling windows

(*schaam*)

Het is dan ook niet de bedoeling te knippen/plakken. Hier op GoT verwachten etc.etc.

Wat er staat (in beide gevallen):
Zoek in bereik A1:H1 naar de tekst die begint met "2:" (of de waarde in cel K1) gevolgd door 0 meer willekeurige tekens ('*'), en retourneer de volledige tekst op die postie in de maxtrix.

[ Voor 22% gewijzigd door Lustucru op 10-11-2013 10:56 ]

[gambieter]

Lustucru schreef op zondag 10 november 2013 @ 09:30:
Het is dan ook niet de bedoeling te knippen/plakken. Hier op GoT verwachten etc.etc.

. Wat dacht je dat ik al probeerde?

Ga ik straks mee aan de slag

[gambieter]

Bingo, dit werkt inderdaad. Ik had wel de COMPARE vertaald, maar de rest niet (en wist niet dat HLOOKUP de HORIZ. ZOEKEN was). Uberhaupt snap ik niet dat Excel die verschillende talen ondersteund, zo lastig met die komma's en punten. Gewoon internationaal (Angelsaksisch) gebruiken

Dit is inderdaad veel beter dan de INDEX-formule, weer wat geleerd. Dank allen

(ik zie al een aantal dingen waarbij ik deze truc kan gebruiken, waar ik eerder de arrays heb gebruikt. Misschien toch maar eens een cursus Excel overwegen )

[ Voor 19% gewijzigd door gambieter op 12-11-2013 01:48 ]