Converting/Exporting data from old table to new table

kirkley08

New Member
Joined
Mar 29, 2023
Messages
20
Office Version
  1. 2021
  2. 2019
  3. 2016
Platform
  1. Windows
Hi everyone,

I'm trying to convert/export the data from my "Database" to the new excel table (different table format).

Other than using "lookup" formula, is there any other way to convert/export it faster? if the entry is 500 or 1000s, it will be time consuming to add the boxes in new table and applying the lookup formula.

Here attached the screenshot of my database and new table. Thanks!
 

Attachments

  • New table.png
    New table.png
    14.2 KB · Views: 14
  • database.png
    database.png
    64.1 KB · Views: 15

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
There is lots of material on Power Query. The ExcelisFun Channel is very good at explaining Power Query
 
Upvote 0
@Skybluekid is right. the ExcelIsFun channel is the best for learning everything Excel and even a little Power BI. That's because many of the playlists are complete college level classes on not just Excel but also Accounting and Statistics, and every video has a sample Workbook with both Start and Finished versions as well as a lot of PDF notes. The playlist for Power Query Playlist can be overwhelming at 83 videos, so give this video a try for starters -#20 in that playlist!. It's 1:49 and well worth the time. Power Query would have saved me HOURS a day in the work I was doing before.
 
Upvote 0
I did not realize that you had posted this question twice.
I offered this solution to you in

Excel Formula:
=IFERROR(INDEX($A$4:$N$8,ROUNDDOWN((ROW(E27)-27)/3,0)+1,
SWITCH(10*(MOD((ROW(E27)-27),3)) +
MOD((COLUMN(E27)-5),10)+1,1,1,2,2,3,3,4,6,5,7,6,5,10,10,14,8,24,9)),"")

which you said was not appropriate because you want to keep your merged cells.
 
Upvote 1
I did not realize that you had posted this question twice.
I offered this solution to you in
yeah well this is my first thread, but I used an image attachment 😅. My friend told me to use minisheet instead so I created the new thread which you already answered me there.
 
Upvote 0
Hi, see the linked file for a possible solution...

It is required to select 7x6 cells (A5:G11) for AutoFill.

The formulas used in the table...
A5: =IF(AND(MOD(ROW(),7)=5,INDEX(Database!A:A,INT((ROW()-5)/7)+4)<>""),INDEX(Database!A:A,INT((ROW()-5)/7)+4),"")
B5: =IF(A5="","",IF(MOD(ROW()+3,7)>LEN(INDEX(Database!B:B,INT((ROW()-5)/7)+4)),"",MID(INDEX(Database!B:B,INT((ROW()-5)/7)+4),MOD(ROW()+3,7),1))) (Range: B5:B10)
C5: =IF(A5="","",LEFT(RIGHT("0"&MONTH(INDEX(Database!C:C,INT((ROW()-5)/7)+4)),2),1))
C6: =IF(A5="","",RIGHT(RIGHT("0"&MONTH(INDEX(Database!C:C,INT((ROW()-5)/7)+4)),2),1))
C7: =IF(A5="","",".")
C8: =IF(A5="","",LEFT(RIGHT("0"&DAY(INDEX(Database!C:C,INT((ROW()-5)/7)+4)),2),1))
C9: =IF(A5="","",LEFT(RIGHT("0"&DAY(INDEX(Database!C:C,INT((ROW()-5)/7)+4)),2),1))
D5: =IF(A5="","",INDEX(Database!E:E,INT((ROW()-5)/7)+4)&INDEX(Database!F:F,INT((ROW()-5)/7)+4))
E5: =IF(A5="","",INDEX(Database!D:D,INT((ROW()-5)/7)+4))
G5: =IF(A5="","",INDEX(Database!I:I,INT((ROW()-5)/7)+4))
D6: =IF(A5="","",INDEX(Database!G:G,INT((ROW()-5)/7)+4))
D7: =IF(A5="","",INDEX(Database!H:H,INT((ROW()-5)/7)+4))

Converting.xlsx

Database.png


NewTable.png


AutoFill.png
 
Upvote 1
Solution
@fjns , does autofill also fill merged cells and the formatting?
If that is the case any of the solutions here will work.
 
Upvote 0

Forum statistics

Threads
1,223,935
Messages
6,175,494
Members
452,649
Latest member
mr_bhavesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top