0

Convert exported table into a list

Matthias Hiese

February 06, 2025 14:34

None

Follow

The range C5:I6 can be exported in the table structure from our EPP system. However, I would like to have the list structure analogous to K5:P9.

Problems:
1. the column header contains information that belongs in cells and this text information must be split into two columns (e.g. F5 => O6 and P6)
2. one of the column headings F5:I5 contains date information Q1:Q4 for quarter 1, quarter 2, etc. Only the start of the year is displayed as the date in column E. In my list, I would like to always display the date from the end of the respective quarter in column M. 

I need your help. Thanks Matthias

 

 

• Facebook
• Twitter
• LinkedIn

4 comments

• 0
  
  CascadiaDataworks February 13, 2025 16:48

  Without understanding your table/data, its a bit hard to give advice.  You are doing a record list so the column C:I is representative of the record.  Is there not a record for each new quarter?  Is that what you are explaining in your point #2?

  How many records would this report typically return?  I have a solution in mind however it may not be overly efficient.

• 0
  
  CascadiaDataworks February 13, 2025 18:35

  If you send an email to my username at gmail, I have a sample that you might be able to use as a guide for your need.

• 0
  
  Matthias Hiese February 14, 2025 06:27

  No, in MS Dynamics NAV the table for the business plan is created in this form. I would not be able to extract individual quarters or months using a date restriction. Plan figures are also recorded in the table structure (columns C:I).
  Of course, it would be possible if I first ran a query for columns C:F and then a query for columns C:E & G and so on.
  For my subsequent processing, however, I need an intelligent data table in Excel. 

  So far I have been able to help myself with power query. But I was hoping that there is a simple solution with Jet functions.

• 0
  
  CascadiaDataworks February 14, 2025 14:57

  Hi Matthias, I have a solution that should work for you that uses a combination of multiple NL Rows, a custom field cache, NL First, and either a VLOOKUP or nested IF statement but it is a lot to try in a message board response (it is an easy solution just many components).  

  Send me an email at my username at gmail.  I've helped others on this board and happy to help you.

  Recorded my solution: https://youtu.be/Z6gwuduFEgs

  Edited by CascadiaDataworks February 17, 2025 21:04