Exporting data from old table to new table

[kirkley08]

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:

This.xlsx
	A	B	C		E	F	G	H	I	J	K	L	M	N
1
2	Reward and Fine List
3	No	Week	Day		Staff	R/F		Article	Desc.	Amount	Consecutive 3	Amount	Consecutive 5	Amount
4	1	W14	04.01		John	R	1	2.1.1	Good Attitude	100
5	2	W14	04.01		Lucy	R	1	2.1.1	Good Attitude	100
6	3	W14	04.01		Kobe	F	1	2.3.2	Late	100
7	4	W14	04.01		Lawrence	R	1	2.1.1	Good Attitude	100
8	5	W14	04.01		Kobe	R	1	2.1.1	Good Attitude	100
9	6	W14	04.01		Kobe	R	2	2.1.1	Good Attitude	100
10	7	W14	04.01		Lawrence	R	2	2.1.1	Good Attitude	100
11	8	W15	04.03		Kobe	R	3	2.1.1	Good Attitude	100	1	600
12	9	W15	04.03		Lawrence	R	3	2.1.1	Good Attitude	100	1	600
13	10	W15	04.03		Baby	F	1	2.3.2	Late	100
14	11	W15	04.03		John	F	1	2.3.2	Late	100
15	12	W15	04.03		Kobe	R	4	2.1.1	Good Attitude	100
16	13	W15	04.03		John	R	1	2.1.1	Good Attitude	100
17	14	W15	04.04		Baby	R	1	2.1.1	Good Attitude	100
18	15	W15	04.04		Kobe	R	5	2.1.1	Good Attitude	100			1	1000
Database

Cell Formulas
Range	Formula
K4:K18	K4	=IFERROR(IF(AND(MOD(G4,3)=0,G4<>0),INT(G4/3),""),"")
L4:L18	L4	=IF(COUNTBLANK(K4)=1,"",600)
M4:M18	M4	=IFERROR(IF(AND(MOD(G4,5)=0,G4<>0),INT(G4/5),""),"")
N4:N18	N4	=IF(COUNTBLANK(M4)=1,"",1000)
A4:A18	A4	=IF(COUNTA(E4)=1,COUNTA($E$3:E3),"")
G4:G18	G4	=IF(COUNTA(E4)=0,"",IF(COUNTIFS($D$2:D4,D4)=1,1,IF(D4=D3,G3+1,IF(AND(E4=E3,F4<>F3),1,IF(AND(E4<>E3,MAX(IF($D$2:D3=D4,$A$2:A3))>=MAX(IF($D$2:D3=(E4&VLOOKUP(F4,$P$1:$Q$2,2,)),$A$2:A3))),VLOOKUP(MAX(IF($D$2:D3=D4,$A$2:A3)),$A$2:G3,7,)+1,1)))))
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Database!$A$3:$N$103	G4:G18

to this new table format:

This.xlsx
	A	B	C	D	E	F	G	H	I	J
1
2	Reward and Fine List
3	No	W	D	Staff.Description				Fine		Reward
4
5	32	W14	04.01	R1		John				100
6				2.1.1
7				Good Attitude
8	32	W14	04.01	R1		Lucy				100
9				2.1.1
10				Good Attitude
11
12
13
New Table

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

Thanks!

 

[awoohaw]

and apparently Borders do not copy with the xl2bb add in.

 

[kirkley08]

Okay, this has the second part of that concatenated value in the next cell. If that isn't what your managment needs then you need to insert an IF to concatenate. Maybe you can see how I built the INDEX/SWITCH formula to figure out how to contatenate that in there (would append an "& ... to the end of the entire formula.

wow, the formula you made is new to me, it takes time to understand but I understand how it works now.

it's not really the answer I'm looking for (since the table format need to be that way), I figured it would be impossible since there are merged cells. But thank you Awoohaw for answering me on this matter.
I really appreciate it! I'm learning something new!

 

[awoohaw]

wow, the formula you made is new to me, it takes time to understand but I understand how it works now.

it's not really the answer I'm looking for (since the table format need to be that way), I figured it would be impossible since there are merged cells. But thank you Awoohaw for answering me on this matter.
I really appreciate it! I'm learning something new!

then i'd suggest using the formula above and then record a macro formatting the merged cells. Then putting the macro in a loop for the number of every 3 line records you have.

 

[Fluff]

Duplicate to: Converting/Exporting data from old table to new table

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.