Celverwijzing met verschuiving of met vaste waardes

[Superjeap]

Vanuit een applicatie heb ik een overzicht gekregen van alle debiteuren. Het is te openen met Excel maar de opmaak is dusdanig dat ik er weinig mee kan.
Het mooie is wel dat er een vast patroon in zit, dus als ik met een lookup de debiteur heb gevonden weet ik dat ik 4 regels moet zakken en van kolom A naar kolom H om daar het adres op te halen.
Alle cellen die de juiste waarde bevatten heb ik (dmv match en wat .samenvoegen) onder elkaar in een nieuw tabblad.
Dus bij Debiteur 123 moet ik ophalen cel H340, cel H344 en cel H348
Met een celverwijzing lukt het niet om vaste waarde naar voren te toveren, die verwijst namelijk naar de cel en niet naar de inhoud van de cel wat ik nu dus wel nodig heb.

Mijn doel is om in 1 regel te krijgen: Debiteur | bedrijfsnaam | straat | pc en plaats | land | etc

Heeft iemand een mooie optie voor dit verhaal? Of een verwijzing naar een oplossing uit gelijkwaardige vraag (flink lopen zoeken hier maar niets kunnen vinden).

In de bijlage Voorbeeldsheet met 2 debiteuren (de totale lijst bestaat uit 30.000 regels met honderden debiteuren)

 

[Mde]

Wat krijg je uit de applicatie binnen?
Misschien is het mogelijk om het anders in te voeren in excel.

 

[Superjeap]

Het is de export functie van de applicatie die dit zo genereert als xls file

 

[VenA]

Met een macro. De uitkomst komt in 'Sheet1'

Sub VenA()
  ar = Sheets("Blad1").UsedRange
  Set d = CreateObject("Scripting.Dictionary")
  For j = 1 To UBound(ar) Step 152
    d(d.Count + 1) = Array(ar(j, 2), ar(j + 4, 8), ar(j + 5, 8), ar(j + 7, 8), ar(j + 11, 8))
  Next j
  Sheets("Sheet1").Cells(1).Resize(d.Count, 5) = Application.Index(d.items, 0, 0)
End Sub

 

[HSV]

+1 = +100

 

[VenA]

+0 = -0:d

Iets anders geschreven wat bij sommigen de voorkeur heeft al maakt het niets uit voor de denkrichting.

Sub VenA1() 
 ar = Sheets("Blad1").UsedRange
  With CreateObject("Scripting.Dictionary")
    For j = 1 To UBound(ar) Step 152
      .Item(.Count) = Array(ar(j, 2), ar(j + 4, 8), ar(j + 5, 8), ar(j + 7, 8), ar(j + 11, 8))
    Next j
    Sheets("Sheet1").Cells(1).Resize(.Count, 5) = Application.Index(.items, 0)
  End With
End Sub

 

[AlexCEL]

Onze vriend Power Query kan weer helpen om van "rotzooi" weer een mooie tabel te maken. Wel consistent de labels invullen...

 

[cow18]

@Alexcel,
ik kan PQ al grotendeels meelezen, maar nabouwen nog niet, dus :thumb:

Dan mijn oplossing met VBA en ik probeer, zonder voorkennis, zoveel mogelijk data mee te nemen.
Voorlopig nog niet alles, dat volgt eventueel nog.

 

[Superjeap]

Super bedankt mensen!
Ik zal jullie oplossingen gaan proberen.
:thumb: