Converting/Exporting data from old table to new table

[kirkley08]

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!

 

[Skybluekid]

Have you tried using Power Query?

 

[kirkley08]

Have you tried using Power Query?

ermmm, not yet, this is my first time hearing about this

 

[Skybluekid]

There is lots of material on Power Query. The ExcelisFun Channel is very good at explaining Power Query

 

[jdellasala]

@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.

 

[awoohaw]

I did not realize that you had posted this question twice.
I offered this solution to you in

Exporting data from old table to new table

Hi everyone, good day! I'm trying to convert/export the data from my "Database" to the new excel table (different table format). From this: ABCEFGHIJKLMN12Reward and Fine List3NoWeekDayStaffR/FArticleDesc.AmountConsecutive 3AmountConsecutive 5Amount41W1404.01JohnR12.1.1Good...

www.mrexcel.com

=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.

 

[kirkley08]

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.

 

[fjns]

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

 

[awoohaw]

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

 

[fjns]

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

@awoohaw
Yes, AutoFill also fills the formatting.
(I only used in the headers merged cells.)