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]

I don't think it is possible to have tables with merged cells. And, in general merged cells is not considered an excel best practice.

But, to look into your question, yes, it can be done. But you have data in your expectations that are not present in your initial table. what cells are the yellow highlighted data located in?

mr excel questions 22.xlsm
	E	F	G	H	I	J	K	L	M	N
22	No	W	D	Staff.Description				Fine		Reward
23
24	32	W14	45017	R1		John				100
25				2.1.1
26				Good Attitude
Sheet3

 

[kirkley08]

I don't think it is possible to have tables with merged cells. And, in general merged cells is not considered an excel best practice.

But, to look into your question, yes, it can be done. But you have data in your expectations that are not present in your initial table. what cells are the yellow highlighted data located in?

mr excel questions 22.xlsm
	E	F	G	H	I	J	K	L	M	N
22	No	W	D	Staff.Description				Fine		Reward
23
24	32	W14	45017	R1		John				100
25				2.1.1
26				Good Attitude
Sheet3

Ohh yeah sorry my bad, for the second table I forgot to change the order number when making minisheet. Here's the correct one for expected table.

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	1	W14	04.01	R1		John				100
6				2.1.1
7				Good Attitude
8	2	W14	04.01	R1		Lucy				100
9				2.1.1
10				Good Attitude
11
12
13
New Table

Yellow highlighted cells:
1. E24 : Initial Table column A:A
2. H24 : Initial Table column F:F
3. H26 : Initial Table column I:I
4. N24 : Initial Tabel column J:J << for this one I was thinking if it can also read the condition if R then will go to "Reward", if F then to "Fine". If it's not possible then I will just divide column J:J in initial table to two (one for reward, one for fine)

 

[awoohaw]

thanks for theupdated information.
Just curious, is this for a report or something?
You only have one value in each of the 2nd and 3rd rows. If you want a table, you can't have related data of one row on another.

 

[awoohaw]

For my question about H24, is it concatenated F:F & G:G, where does the number come from, if not G:G?

 

[kirkley08]

thanks for theupdated information.
Just curious, is this for a report or something?
You only have one value in each of the 2nd and 3rd rows. If you want a table, you can't have related data of one row on another.

Yes this is for a report, but the report need to be in the form of the expected table.
You only have one value in each of the 2nd and 3rd rows. If you want a table, you can't have related data of one row on another. << you mean in order to make a table, the data from the initial table need to be standalone value, can't have it to be related/read the conditions in other rows? Is that correct?

 

[kirkley08]

For my question about H24, is it concatenated F:F & G:G, where does the number come from, if not G:G?

yes it's concatenated. but if my understanding of "can't have related data of one row on another" is correct, means I need to make another column for concatenated value first before I want it to be exported to expected table right?

 

[awoohaw]

no, i'm talking about excel tables. in databases you can have related data in other tables. the 2nd and 3rd row are essentially related data (to whatever key is in the first row)... but spreadsheet excel doesn't workthat way. It can with Power Pivot, I think (I am not expert on that, i'm barely well advanced in excel formulas).

But, here is a stab at it. I did not concatenate G and H in this, and that would actually complicate this formula even more so. This doesn't even have H being mapped, I'll post an update in a minute, but you can peek at this and see if it satisfies (Look at the green, I'll explain what you need to do to fit it into your workbook as well).

mr excel questions 22.xlsm
	A	B	C	D	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	45017		John	R	1	2.1.1	Good Attitude	100
5	2	W14	45017		Lucy	R	2	2.1.1	Good Attitude	100
6	1	2	3	4	5	6	7	8	9	10	11	12	13	14
7	4	W14	45017		Lawrence	R	#N/A	2.1.1	Good Attitude	100
8	5	W14	45017		Kobe	R	#N/A	2.1.1	Good Attitude	100
20
21					Reward and Fine List
22					No	W	D	Staff.Description				Fine		Reward
23
24					32	W14	45017	R1		John				100
25								2.1.1
26								Good Attitude
27				1	1	W14	45017	R		John				100
28				1				2.1.1
29				1				Good Attitude
30				2	2	W14	45017	R		Lucy				100
31				2				2.1.1
32				2				Good Attitude
33				3	1	2	3	6		5				10
34				3				8
35				3				9
36				4	4	W14	45017	R		Lawrence				100
37				4				2.1.1
38				4				Good Attitude
kirkley08

Cell Formulas
Range	Formula
K4:K5,K7:K8	K4	=IFERROR(IF(AND(MOD(G4,3)=0,G4<>0),INT(G4/3),""),"")
L4:L5,L7:L8	L4	=IF(COUNTBLANK(K4)=1,"",600)
M4:M5,M7:M8	M4	=IFERROR(IF(AND(MOD(G4,5)=0,G4<>0),INT(G4/5),""),"")
N4:N5,N7:N8	N4	=IF(COUNTBLANK(M4)=1,"",1000)
A4:A5,A7:A8	A4	=IF(COUNTA(E4)=1,COUNTA($E$3:E3),"")
G4:G5,G7:G8	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,$E$20:$F$21,2,)),$A$2:A3))),VLOOKUP(MAX(IF($D$2:D3=D4,$A$2:A3)),$A$2:G3,7,)+1,1)))))
D27:D38	D27	=ROUNDDOWN((ROW(E27)-27)/3,0)+1
E27:N38	E27	=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,6,5,10,10,14,8,24,9)),"")

 

[awoohaw]

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.

Now, to get the formula working in your workbook. I hope you are using 2021, as I'm not sure if SWITCH is available other than 2021 and 365 (and I'm not sure about 2021).

In the formulas where you see the ROW and COLUMN functions. I'm subtracting the row or column number from the top left cell the formula begins in. So, if you paste the formula into A2, the ROW component would be (ROW(A2)-2), and the COLUMN component would be (COLUMN(A2)-1).

(Edit: I have pasted this twice, hopefully to get borders)

mr excel questions 22.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
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	45017		John	R	1	2.1.1	Good Attitude	100
5	2	W14	45017		Lucy	R	2	2.1.1	Good Attitude	100
6	1	2	3	4	5	6	7	8	9	10	11	12	13	14
7	4	W14	45017		Lawrence	R	#N/A	2.1.1	Good Attitude	100
8	5	W14	45017		Kobe	R	#N/A	2.1.1	Good Attitude	100
9	6	W14	45017		Kobe	R	#N/A	2.1.1	Good Attitude	100
10	7	W14	45017		Lawrence	R	#N/A	2.1.1	Good Attitude	100
11	8	W15	45019		Kobe	R	#N/A	2.1.1	Good Attitude	100
12	9	W15	45019		Lawrence	R	#N/A	2.1.1	Good Attitude	100
13	10	W15	45019		Baby	F	#N/A	2.3.2	Late	100
14	11	W15	45019		John	F	#N/A	2.3.2	Late	100
15	12	W15	45019		Kobe	R	#N/A	2.1.1	Good Attitude	100
16	13	W15	45019		John	R	#N/A	2.1.1	Good Attitude	100
17	14	W15	45020		Baby	R	#N/A	2.1.1	Good Attitude	100
18	15	W15	45020		Kobe	R	#N/A	2.1.1	Good Attitude	100
19
20
21					Reward and Fine List
22					No	W	D	Staff.Description				Fine		Reward
23
24					32	W14	45017	R1		John				100
25								2.1.1
26								Good Attitude
27				1	1	W14	45017	R	1	John				100
28				1				2.1.1
29				1				Good Attitude
30				2	2	W14	45017	R	2	Lucy				100
31				2				2.1.1
32				2				Good Attitude
33				3	1	2	3	6	7	5				10
34				3				8
35				3				9
36				4	4	W14	45017	R		Lawrence				100
37				4				2.1.1
38				4				Good Attitude
39
kirkley08

Cell Formulas
Range	Formula
K4:K5,K7:K18	K4	=IFERROR(IF(AND(MOD(G4,3)=0,G4<>0),INT(G4/3),""),"")
L4:L5,L7:L18	L4	=IF(COUNTBLANK(K4)=1,"",600)
M4:M5,M7:M18	M4	=IFERROR(IF(AND(MOD(G4,5)=0,G4<>0),INT(G4/5),""),"")
N4:N5,N7:N18	N4	=IF(COUNTBLANK(M4)=1,"",1000)
A4:A5,A7:A18	A4	=IF(COUNTA(E4)=1,COUNTA($E$3:E3),"")
G4:G5,G7: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,$E$20:$F$21,2,)),$A$2:A3))),VLOOKUP(MAX(IF($D$2:D3=D4,$A$2:A3)),$A$2:G3,7,)+1,1)))))
D27:D38	D27	=ROUNDDOWN((ROW(E27)-27)/3,0)+1
E27:N38	E27	=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)),"")

 

[awoohaw]

CORRECTION TO EARLIER STATEMENT:

no, i'm talking about excel tables. in databases you can have related data in other tables. the 2nd and 3rd row are essentially related data (to whatever key is in the first row)

I meant you can't have related data in other ROWS. each row in an excel table is its own record. I have not seen any excel tables where a prior row is linked to a subsequent row.