Creating new table

mmiklauz · Feb 14, 2018

Hi everyone!

I have a problem and I can't figure out how to solve it, so I would appreciate any help.

1. I need to check each row in OLD TABLE, look into HELP TABLE for NEW_ID (if exists) and create new ID in NEW TABLE
2. Then I need sum all values that belong to NEW_ID and insert into NEW TABLE with NEW_ID

HELP TABLE

NEW_ID	OLD_ID_1	OLD_ID_2	OLD_ID_3	OLD_ID_4
1	11	22	33
2	66	66	88
3	77	77
4	44	55	88	99

OLD TABLE

ID	VALUE
11	1
66	3
22	7
88	9
33	5
77	2
99	4
55	6
44	4
88	7

NEW TABLE

ID	VALUE
1	10
2	12
3	13
4	2

Aladin Akyurek · Feb 14, 2018

The totals you show in NEW TABLE are not correct...

Row\Col	A	B	C	D	E	F	G	H	I	J	K
1	NEW_ID	OLD_ID_1	OLD_ID_2	OLD_ID_3	OLD_ID_4		ID	VALUE		ID	VALUE
2	1	11	22	33			11	1		1	13
3	2	66	66	88			66	3		2	22
4	3	77	77				22	7		3	4
5	4	44	55	88	99		88	9		4	30
6							33	5
7							77	2
8							99	4
9							55	6
10							44	4
11							88	7

In J2 control+shift+enter, not just enter, and copy down:

Rich (BB code):

=IF(ROWS($J$2:J2)>COUNT($A$2:$A$5),"",INDEX($A$2:$A$5,
    SMALL(IF(FREQUENCY(IF(ISNUMBER($A$2:$A$5),IF(COUNTIFS($G$2:$G$11,$B$2:$E$5),
    MATCH($A$2:$A$5,$A$2:$A$5,0))),ROW($A$2:$A$5)-ROW($A$2)+1),ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($J$2:J2))))

In K2 control+shift+enter, not just enter, and copy down:

Rich (BB code):

=SUM(SUMIFS($H$2:$H$11,$G$2:$G$11,INDEX($B$2:$E$5,MATCH($J2,$A$2:$A$5,0),0)))

mmiklauz · Feb 14, 2018

Aladin Akyurek said:
The totals you show in NEW TABLE are not correct...

Row\Col
A
B
C
D
E
F
G
H
I
J
K
1
NEW_ID OLD_ID_1 OLD_ID_2 OLD_ID_3 OLD_ID_4 ID VALUE ID VALUE
2
1
11
22
33
11
1
1
13
3
2
66
66
88
66
3
2
22
4
3
77
77
22
7
3
4
5
4
44
55
88
99
88
9
4
30
6
33
5
7
77
2
8
99
4
9
55
6
10
44
4
11
88
7

<tbody>
</tbody>

In J2 control+shift+enter, not just enter, and copy down:

Rich (BB code):

=IF(ROWS($J$2:J2)>COUNT($A$2:$A$5),"",INDEX($A$2:$A$5, SMALL(IF(FREQUENCY(IF(ISNUMBER($A$2:$A$5),IF(COUNTIFS($G$2:$G$11,$B$2:$E$5), MATCH($A$2:$A$5,$A$2:$A$5,0))),ROW($A$2:$A$5)-ROW($A$2)+1),ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($J$2:J2))))

In K2 control+shift+enter, not just enter, and copy down:

Rich (BB code):

=SUM(SUMIFS($H$2:$H$11,$G$2:$G$11,INDEX($B$2:$E$5,MATCH($J2,$A$2:$A$5,0),0)))

Thank you!! That works great!!

Aladin Akyurek · Feb 14, 2018

mmiklauz said:
Thank you!! That works great!!

You are welcome.

mmiklauz · Feb 14, 2018

Aladin Akyurek said:
The totals you show in NEW TABLE are not correct...

Row\Col
A
B
C
D
E
F
G
H
I
J
K
1
NEW_ID OLD_ID_1 OLD_ID_2 OLD_ID_3 OLD_ID_4 ID VALUE ID VALUE
2
1
11
22
33
11
1
1
13
3
2
66
66
88
66
3
2
22
4
3
77
77
22
7
3
4
5
4
44
55
88
99
88
9
4
30
6
33
5
7
77
2
8
99
4
9
55
6
10
44
4
11
88
7

<tbody>
</tbody>

In J2 control+shift+enter, not just enter, and copy down:

Rich (BB code):

=IF(ROWS($J$2:J2)>COUNT($A$2:$A$5),"",INDEX($A$2:$A$5, SMALL(IF(FREQUENCY(IF(ISNUMBER($A$2:$A$5),IF(COUNTIFS($G$2:$G$11,$B$2:$E$5), MATCH($A$2:$A$5,$A$2:$A$5,0))),ROW($A$2:$A$5)-ROW($A$2)+1),ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($J$2:J2))))

In K2 control+shift+enter, not just enter, and copy down:

Rich (BB code):

=SUM(SUMIFS($H$2:$H$11,$G$2:$G$11,INDEX($B$2:$E$5,MATCH($J2,$A$2:$A$5,0),0)))

One more question..how can I change the formula to do the following:

HELP TABLE

NEW_ID	OLD_ID_1	OLD_ID_2	OLD_ID_3	OLD_ID_4
1	11	22	33
2	66	88
3	77
4	44	55	88	99

OLD TABLE

ID	ITEM_NO	VALUE
11	1	1
11	2	3
11	3	7
22	1	9
22	2	5
22	3	2
33	1	4
33	2	6
33	3	4

NEW TABLE

ID	ITEM_NO	VALUE
1	1	14
1	2	14
1	3	13

Thank you in advance!

Fluff · Feb 14, 2018

@mmiklauz

Please do not post the same question multiple times.
Note that sometimes posts from new users require Moderator approval before you can see them on the public forums. When this happens, you should see a message to that effect when you try to post it.
Please be patient and do not attempt to post the question again.

I have removed your duplicate post

Aladin Akyurek · Feb 17, 2018

mmiklauz said:
One more question..how can I change the formula to do the following:

[...]

The output representation you have is not viable. In what follows I use a matrix representation...

Row\Col	A	B	C	D	E	G	H	I	K	L	M	N
1	NEW_ID	OLD_ID_1	OLD_ID_2	OLD_ID_3	OLD_ID_4	ID	ITEM_NO	VALUE		ITEM_NO
2	1	11	22	33		11	1	1	ID	1	2	3
3	2	66	88			11	2	3	1	14	14	13
4	3	77				11	3	7	2	0	0	0
5	4	44	55	88	99	22	1	9	3	0	0	0
6						22	2	5	4	0	0	0
7						22	3	2
8						33	1	4
9						33	2	6
10						33	3	4

In K3 control+shift+enter, not just enter, and copy down:

Rich (BB code):

=IF(ROWS($K$3:K3)>COUNT($A$2:$A$5),"",INDEX($A$2:$A$5,SMALL(IF(FREQUENCY(IF(ISNUMBER($A$2:$A$5),
    MATCH($A$2:$A$5,$A$2:$A$5,0)),ROW($A$2:$A$5)-ROW($A$2)+1),ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($K$3:K3))))

In L2 control+shift+enter, not just enter, and copy across:

Rich (BB code):

=IFERROR(INDEX($H$2:$H$10,SMALL(IF(FREQUENCY(IF(ISNUMBER($H$2:$H$10),MATCH($H$2:$H$10,$H$2:$H$10,0)),
    ROW($H$2:$H$10)-ROW($H$2)+1),ROW($H$2:$H$10)-ROW($H$2)+1),COLUMNS($L$2:L2))),"")

In L3 control+shift+enter, not just enter, copy across, and down:

Rich (BB code):

=SUM(SUMIFS($I$2:$I$10,$G$2:$G$10,IF($A$2:$A$5=$K3,$B$2:$E$5),$H$2:$H$10,L$2))

mmiklauz · Feb 21, 2018

Aladin Akyurek said:
The output representation you have is not viable. In what follows I use a matrix representation...

Row\Col
A
B
C
D
E
G
H
I
K
L
M
N
1
NEW_ID OLD_ID_1 OLD_ID_2 OLD_ID_3 OLD_ID_4 ID ITEM_NO VALUE
ITEM_NO
2
1
11
22
33
11
1
1
ID
1
2
3
3
2
66
88
11
2
3
1
14
14
13
4
3
77
11
3
7
2
0
0
0
5
4
44
55
88
99
22
1
9
3
0
0
0
6
22
2
5
4
0
0
0
7
22
3
2
8
33
1
4
9
33
2
6
10
33
3
4

<tbody>
</tbody>

In K3 control+shift+enter, not just enter, and copy down:

Rich (BB code):

=IF(ROWS($K$3:K3)>COUNT($A$2:$A$5),"",INDEX($A$2:$A$5,SMALL(IF(FREQUENCY(IF(ISNUMBER($A$2:$A$5), MATCH($A$2:$A$5,$A$2:$A$5,0)),ROW($A$2:$A$5)-ROW($A$2)+1),ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($K$3:K3))))

In L2 control+shift+enter, not just enter, and copy across:

Rich (BB code):

=IFERROR(INDEX($H$2:$H$10,SMALL(IF(FREQUENCY(IF(ISNUMBER($H$2:$H$10),MATCH($H$2:$H$10,$H$2:$H$10,0)), ROW($H$2:$H$10)-ROW($H$2)+1),ROW($H$2:$H$10)-ROW($H$2)+1),COLUMNS($L$2:L2))),"")

In L3 control+shift+enter, not just enter, copy across, and down:

Rich (BB code):

=SUM(SUMIFS($I$2:$I$10,$G$2:$G$10,IF($A$2:$A$5=$K3,$B$2:$E$5),$H$2:$H$10,L$2))

Thank you, this will help me!

Aladin Akyurek · Feb 21, 2018

mmiklauz said:
Thank you, this will help me!

You are welcome. Thanks for the update.

Creating new table

mmiklauz

New Member

Excel Facts

Aladin Akyurek

MrExcel MVP

mmiklauz

New Member

Aladin Akyurek

MrExcel MVP

mmiklauz

New Member

Fluff

MrExcel MVP, Moderator

Aladin Akyurek

MrExcel MVP

mmiklauz

New Member

Aladin Akyurek

MrExcel MVP

Similar threads

Forum statistics

Share this page

Creating new table

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP, Moderator

MrExcel MVP

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock