Kolommen van naast elkaar, onder elkaar zonder lege velden

[Beachbar]

Mijn vraag:
Uit een SQL database (uit onze CRM) heb ik via een query een tabel in Excel. Die kan verschillen qua aantal rijden. Daarvan moet een lijst worden gemaakt naar .csv om in te lezen in de telefooncentrale. Daarin worden twee kolommen gevraagd: Omschrijving en telefoonnummer.
Echter in de tabel staan per regel 8 verschillende telefoonnummers, bijvoorbeeld voor Fax (ja bestaat nog ), Vast nummer, Mobiel. Hoe krijg ik die onder elkaar zonder dat er lege regels tussenkomen?
Ik kan zelf wel een unieke omschrijving maken (a.d.h.v. de gegevens die eerder in de rij voorkomen. Uiteraard moet die wel naast het goede telefoonnummer komen te staan, maar dat zou wrschnlk met de dezelfde formule kunnen waarmee ik ook het telefoonnummer vind (zo niet, stel gerust een andere formule daarvoor).

I.v.m. de privacy upload ik de Excellijst niet, ik hoop op jullie begrip.

Relevante software en hardware die ik gebruik:
MS Excel 2013 (Nederlandstalig)

Wat ik al gevonden of geprobeerd heb:
uitermate gegoogeld; ik kom op: "Verschuiven" (Ofsett), "Index", Powerquery etc, maar vind geen eenvoudige te volgen oplossing. Ook veel posts van Tweakers nagezien.

Achter de uitkomst van de query in Excel heb ik formules gemaakt van aantal telefoonnummers die gevuld zijn (met de functie "arg"), het kolomnummer waar van kolommen waar telefoonnummers in staan etc. Dan zou ik eventueel kunnen terugvinden als er een foutmelding ontstaat bij het inlezen (zoals dat duplicaten zijn niet toegestaan) etc.

Ik ben benieuwd of jullie een oplossing weten (niet in VBA)

[Hero of Time]

Je kan toch data fingeren om een idee te geven hoe de data eruit ziet en hoe je het wilt hebben? Het is niet echt duidelijk wat je nou hebt en wilt.

[naitsoezn]

Als het je wel lukt om de data onder elkaar te krijgen met witte cellen ertussen, dan kun je ook daarna een filter toepassen (i.c.m. Copy -> Paste in een nieuwe kolom) om de data over te houden zonder witte cellen.

[D-dark]

Denk dat je dit in meerdere delen moet opknippen. Paar draaitabellen maken met bv alleen de fax nr;s , semafoonnummers, tel nr's daarin de lege regels eruitfilteren en de resultaten samenvoegen tot 1 lijst.

[Paultje3181]

Met vba een loop maken voor alle kolommen en alle rijen.

Visual Basic:

Sub LastRowInOneColumn()
'Find the last used row in a Column: column A in this example
    Dim LastRow As Long
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    MsgBox LastRow
End Sub

Sub LastColumnInOneRow()
'Find the last used column in a Row: row 1 in this example
    Dim LastCol As Integer
    With ActiveSheet
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    End With
    MsgBox LastCol
End Sub

En zo dus alle cellen langs gaan.

[ShitHappens]

Alternatief is nog de database query aanpassen en het via SQL oplossen, dat je de hele Excel stap kunt overslaan en het resultaat meteen kunt exporteren naar CSV?

Stel je voor, dat je de volgende tabel hebt:

Klant naam	Vast nummer 1	Vast nummer 2	Mobiel	Fax
Pietje	0200000000	NULL	0600000000	020000000
Jantje	0200000001	0200000002	0600000001	NULL

Zou je nog zelfs met een aantal UNION ALL's de resultaten per kolom achter elkaar kunnen plakken.

SQL:

SELECT
     CONCAT([Klant naam], ' vast nummer 1') AS omschrijving,
     [Vast nummer 1] AS nummer
FROM klanten
WHERE [Vast nummer 1] IS NOT NULL

UNION ALL

SELECT
     CONCAT([Klant naam], ' vast nummer 2') AS omschrijving,
     [Vast nummer 2] AS nummer
FROM klanten
WHERE [Vast nummer 2] IS NOT NULL

etc, herhaal zo per kolom

Geeft dan als output precies wat je zoekt
(Dit kan waarschijnlijk nog wel netter, dit is wat mij tot nu toe altijd geholpen heeft)

[Beachbar]

Hero of Time schreef op vrijdag 23 augustus 2019 @ 19:12:
Je kan toch data fingeren om een idee te geven hoe de data eruit ziet en hoe je het wilt hebben? Het is niet echt duidelijk wat je nou hebt en wilt.

Dank voor de reactie. Idd heb ik overwogen om een voorbeeld mee te zenden. Maar inmiddels heeft "ShitHappens" (zie zijn bericht hierna) dit al gedaan (waarvoor dank).

[Beachbar]

naitsoezn schreef op vrijdag 23 augustus 2019 @ 19:19:
Als het je wel lukt om de data onder elkaar te krijgen met witte cellen ertussen, dan kun je ook daarna een filter toepassen (i.c.m. Copy -> Paste in een nieuwe kolom) om de data over te houden zonder witte cellen.

Dank Agee voor je reactie. Ik begrijp wat je bedoelt, maar ik heb het liefst zo min mogelijk stappen om tot het eindresultaat (csv) te komen. De import zal vaker moeten gebeuren en ik wil de kans dat er door een vergeten stap wat mis minimaliseren.

[Beachbar]

D-dark schreef op vrijdag 23 augustus 2019 @ 21:05:
Denk dat je dit in meerdere delen moet opknippen. Paar draaitabellen maken met bv alleen de fax nr;s , semafoonnummers, tel nr's daarin de lege regels eruitfilteren en de resultaten samenvoegen tot 1 lijst.

Dag D-Dark, Dank voor je suggestie. Deze variant (met 8 tabbladen) heb ik ook al in concept gereed staan. Dat lost ook het probleem op dat er lege regels tevoorschijn komen (want regels met nihiltelefoonnummers kan ik uitsluiten in SQL). Maar de tabbladen verschillen dan van aantal regels. Dan moet ik dit ondervangen in Excel.

[Beachbar]

Paultje3181 schreef op zondag 25 augustus 2019 @ 17:29:
Met vba een loop maken voor alle kolommen en alle rijen.

Visual Basic:

Sub LastRowInOneColumn()
'Find the last used row in a Column: column A in this example
    Dim LastRow As Long
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    MsgBox LastRow
End Sub

Sub LastColumnInOneRow()
'Find the last used column in a Row: row 1 in this example
    Dim LastCol As Integer
    With ActiveSheet
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    End With
    MsgBox LastCol
End Sub

En zo dus alle cellen langs gaan.

Dank Paultje 3181; dank voor je meedenken. Maar ik heb (bijna) geen kennis van VBA. Het bestand zal misschien onderhoud nodig hebben en dan grijp ik liever terug naar functies die ik wel (een beetje) ken. Wel gaaf dat je een voorbeeld code in VBA mee zendt.

[F_J_K]

Welkom!

Als je de database rechtstreeks kan benaderen en er SQL kan uitvoeren, dan zou ik idd de tip van ShitHappens in "Kolommen van naast elkaar, onder elkaar zonder lege velden" gebruiken. Natuurlijk wel aanpassen naar de daadwerkelijke DB. (En voldoende snappen wat er staat, nooit andermans code blind draaien )

Aantal regels mag voor csv toch geen probleem zijn?

offtopic:
Als je binnen 24u wilt reageren en er heeft nog niemand anders gereageerd, dan ziet men liever dat je je vorige post aanpast dan dat je een nieuwe reactie plaatst. Het wordt zo gezien als je topic 'omhoog schoppen' om de bovenaan te blijven.

[Beachbar]

ShitHappens schreef op zondag 25 augustus 2019 @ 17:53:
Alternatief is nog de database query aanpassen en het via SQL oplossen, dat je de hele Excel stap kunt overslaan en het resultaat meteen kunt exporteren naar CSV?

Stel je voor, dat je de volgende tabel hebt:

Klant naam	Vast nummer 1	Vast nummer 2	Mobiel	Fax
Pietje	0200000000	NULL	0600000000	020000000
Jantje	0200000001	0200000002	0600000001	NULL

Zou je nog zelfs met een aantal UNION ALL's de resultaten per kolom achter elkaar kunnen plakken.

SQL:

SELECT
     CONCAT([Klant naam], ' vast nummer 1') AS omschrijving,
     [Vast nummer 1] AS nummer
FROM klanten
WHERE [Vast nummer 1] IS NOT NULL

UNION ALL

SELECT
     CONCAT([Klant naam], ' vast nummer 2') AS omschrijving,
     [Vast nummer 2] AS nummer
FROM klanten
WHERE [Vast nummer 2] IS NOT NULL

etc, herhaal zo per kolom

Geeft dan als output precies wat je zoekt
(Dit kan waarschijnlijk nog wel netter, dit is wat mij tot nu toe altijd geholpen heeft)

Dag ShitHappens, tnx voor je reactie en voorbeeldcode. Ik heb ook proberen te 'prutsen' in SQL, maar dan met de mogelijkheden die MsSQL biedt (klein progje die start als je in Excel een query wilt bewerken). Dat leek te beperkt (door gebrek aan mijn kunde); Ik heb o.a. (inner en outer) joins geprobeerd, maar kreeg het resultaat niet. <<Ik zal je voorstel (zo mogelijk vanavond) nog even nader bekijken; wordt vervolgd. >> Inmiddels verder. Ik dacht eerst dat je een spelfout maakte, want ik kende de functie CONCAT niet (maar contact bedoelt). Dat kan omdat ik SQL niet ken, maar blijkbaar bestaat het ook in Excel.
Echter als ik de functie invoer in Microsoft Query krijg ik de (fout)melding: " 'CONCAT' is nog a regognized built-in function name.' ". Dit gaat helaas dus niet goed. Via een site van MS lijk ik wel de opmaak van de formule wel goed te hebben gedaan. Wat nu?

[ Voor 11% gewijzigd door Beachbar op 27-08-2019 19:33 . Reden: na uitproberen (nieuwe reactie mocht niet, begreep ik) ]

[Beachbar]

F_J_K schreef op maandag 26 augustus 2019 @ 11:35:
Welkom!

Als je de database rechtstreeks kan benaderen en er SQL kan uitvoeren, dan zou ik idd de tip van ShitHappens in "Kolommen van naast elkaar, onder elkaar zonder lege velden" gebruiken. Natuurlijk wel aanpassen naar de daadwerkelijke DB. (En voldoende snappen wat er staat, nooit andermans code blind draaien )

Aantal regels mag voor csv toch geen probleem zijn?

offtopic:
Als je binnen 24u wilt reageren en er heeft nog niemand anders gereageerd, dan ziet men liever dat je je vorige post aanpast dan dat je een nieuwe reactie plaatst. Het wordt zo gezien als je topic 'omhoog schoppen' om de bovenaan te blijven.

Dag F_J_K, tnx voor je reactie. Deze kruist die van mij op het bericht van ShitHappens. Resumé mijn bericht, zijn voorstel zal ik nader bekijken (nadat / als ik de code snap).

[ Voor 3% gewijzigd door Beachbar op 26-08-2019 11:41 ]

[Beachbar]

Beachbar schreef op maandag 26 augustus 2019 @ 11:40:
[...]
offtopic: Als je binnen 24u wilt reageren en er heeft nog niemand anders gereageerd, dan ziet men liever dat je je vorige post aanpast dan dat je een nieuwe reactie plaatst. Het wordt zo gezien als je topic 'omhoog schoppen' om de bovenaan te blijven.

Dat wist ik niet; ben nieuw op dit forum. Ik begrijp je ook niet helemaal. Of doe ik nu juist wat je afraadt (door te reageren op je bericht; dan sorry!). Ik wil gewoon iedereen die reageert netjes bedanken door te reageren.

[Hero of Time]

Beachbar schreef op maandag 26 augustus 2019 @ 20:57:
[...]

Dat wist ik niet; ben nieuw op dit forum. Ik begrijp je ook niet helemaal. Of doe ik nu juist wat je afraadt (door te reageren op je bericht; dan sorry!). Ik wil gewoon iedereen die reageert netjes bedanken door te reageren.

Wat er wordt bedoelt is dat je niet in korte tijd reacties na elkaar moet plaatsen als er geen ander heeft gereageerd. Je kan posts bewerken door op de 'wijzig' knop te drukken. Als je iemand quote (of op de knop 'Reageer' onder de quick reply klikt, een voorbeeld wilt zien, etc), kan je omlaag scrollen om de andere reacties te zien en zo op meerdere mensen in een enkel bericht reageren.

Het is daarom een idee om nog even de FAQs die we hebben na te lezen. Begin met Het algemeen beleid, dan heb je een idee over de algemene regels op het forum.

[Beachbar]

@Hero of Time en @F_J_K Ik hoop dat ik nu niet te snel reageer.

Want ik heb de suggestievan @ShitHappens geprobeerd, maar de functie werkt niet (ook als ik een afgeslankte vorm van de voorgestelde querie met CONCAT probeer). Ik krijg dan een foutmelding.

Ik ben benieuwd naar reacties, want ik vind de oplossingsrichting wel goed. Een collega stelde vandaag voor het via MsAccess te proberen, maar dat programma ken ik (net als SQL ook) niet zo goed en dan moet ik er een ander programma bijhalen, terwijl ik tot nu toe binnen Excel wilde blijven (dat ik wel een beetje ken).

[ShitHappens]

Goed te horen dat je 't inderdaad in die richting aan het proberen bent Wat was de foutmelding, en heb je al gezocht naar wat deze betekent? Alleen al de foutmelding zelf in Google gooien kan al de goeie kant op helpen. En de foutmeldingen geven meestal tot op de regel nauwkeurig aan waar de fout zit.
In m'n voorbeeldquery heb ik trouwens aangenomen dat het om MSSQL gaat, maar op MySQL gaat 'ie bijvoorbeeld niet onmiddellijk werken.

De omweg via MS Access te gaan vind ik wel erg vreemd. In m'n 6 jaar ervaring met datamigraties heb ik er nog nooit een noodzaak voor gezien, omdat je zoveel al met SQL kunt oplossen.

Dus zou je nog iets beter kunnen omschrijven wat je geprobeerd heb en nu op stukloopt?

EDIT:
Ik zie nu net pas deze update:

Beachbar schreef op maandag 26 augustus 2019 @ 11:38:
[...]
>> Inmiddels verder. Ik dacht eerst dat je een spelfout maakte, want ik kende de functie CONCAT niet (maar contact bedoelt). Dat kan omdat ik SQL niet ken, maar blijkbaar bestaat het ook in Excel.
Echter als ik de functie invoer in Microsoft Query krijg ik de (fout)melding: " 'CONCAT' is nog a regognized built-in function name.' ". Dit gaat helaas dus niet goed. Via een site van MS lijk ik wel de opmaak van de formule wel goed te hebben gedaan. Wat nu?

Ik ga even spieken wat MS Query daar precies probeert te doen, enige wat ik me nu kan voorstellen is dat 'ie CONCATENATE wilt zien (zo heet dezelfde functie in Excel) maar da's enkel een gokje. Rechtstreekse toegang met de database vanuit SQL Server Management Studio zou trouwens makkelijker zijn, maar goed

[ Voor 39% gewijzigd door ShitHappens op 28-08-2019 21:10 ]

[Beachbar]

@ShitHappens Tnx voor je reactie, Ik mocht niet te vaak reageren, dus ik hoop dat ik de forumregels nu niet overtreed. In reactie op je vraag: De foutmelding had ik (om de forumregels niet te overtreden) de vorige keer al in mijn bericht opgenomen: "als ik de functie invoer in Microsoft Query krijg ik de (fout)melding: " 'CONCAT' is nog a regognized built-in function name.' ". Dit gaat helaas dus niet goed. Via een help/info site van MS lijk ik wel de opmaak (parameters e.d.) van de formule wel goed te hebben gedaan. "

Ik gebruik geen MySQL, maar open een programma vanuit Excel om SQL te gebruiken.

[ Voor 8% gewijzigd door Beachbar op 28-08-2019 21:27 . Reden: betere beschrijving op reactie van vorige reageerder ]

[Hero of Time]

Beachbar schreef op woensdag 28 augustus 2019 @ 21:09:
Ik mocht niet te vaak reageren, dus ik hoop dat ik de forumregels nu niet overtreed.

Heh, zo streng zijn we nou ook weer niet. Het ging erom dat je losse reacties plaatste om op meerdere mensen te reageren. Dat is niet zo handig om te doen. Als je met iemand van gedachten aan het wisselen bent, is dat iets heel anders. In extreme gevallen zou het om 'slow chat' kunnen gaan, maar daar is nu geen sprake van.

[ShitHappens]

@Beachbar OK, ik probeer even mee te doen:

Een test MSSQL database aangemaakt, met de mock data als in m'n eerdere post:


Vervolgens in Excel door de wizard Get Data > From Database > From SQL Server Database gelopen. Allemaal default settings, en dan de Klanten tabel uit m'n testdatabase. Dat komt dan als volgt door:


Maar er is ook een optie, onder Advanced, om een SQL query in te kloppen:
(een deel van de query valt weg, maar is de volledige query uit de rechter helft van screenshot 1)


En dat komt er dan als volgt uit:


Maar ik kom tot hetzelfde resultaat via Get Data > From Other Sources > From Microsoft Query en de exact zelfde query als in screenshot 1.


Dus... De vraag is nu waarom het bij mij wel werkt zoals ik wil, maar je onder bijna dezelfde omstandigheden tegen problemen aanloopt. Ik heb dan wel Excel 2016 ipv 2013 maar dat zou hier geen verschil mogen maken. Maar bovenstaande laat iig zien dat 't echt wel kan

Zou je desnoods wat meer inzicht kunnen geven met screenshots? Daarbij kun je dan gevoelige data wegpoetsen (iets dat wat vertelt over je werkgever, klantnamen, de daadwerkelijke telefoonnummers etc) met Paint oid.