hi
I have repeated data for each name . so what I want merge duplicate names based on column B and split column C into two columns and if threre repeate item also should merge with comma and summing values in columns D,E and insert column BALANCE to subtract column D from E
first sheet
the result should in second sheet . it should create the whole data with headers
I have repeated data for each name . so what I want merge duplicate names based on column B and split column C into two columns and if threre repeate item also should merge with comma and summing values in columns D,E and insert column BALANCE to subtract column D from E
first sheet
deb.xlsm | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | DATE | NAME | CONDITION | DEBIT | CREDIT | ||
2 | 20/09/2021 | ALI1 | INVOICE1 | 10,000.000 | |||
3 | 21/09/2021 | ALI1 | INVOICE2 | 5,000.000 | |||
4 | 22/09/2021 | ALI3 | INVOICE3 | 20,000.000 | |||
5 | 23/09/2021 | ALI4 | INVOICE4 | 15,000.000 | |||
6 | 24/09/2021 | ALI5 | INVOICE5 | 25,000.000 | |||
7 | 25/09/2021 | ALI6 | INVOICE6 | 30,000.000 | |||
8 | 26/09/2021 | ALI7 | INVOICE7 | 1,500.000 | |||
9 | 27/09/2021 | ALI7 | INVOICE8 | 10,000.000 | |||
10 | 28/09/2021 | ALI9 | INVOICE9 | 2,000.000 | |||
11 | 29/09/2021 | ALI10 | INVOICE10 | 3,500.000 | |||
12 | 30/09/2021 | ALI11 | INVOICE11 | 1,500.000 | |||
13 | 01/10/2021 | ALI12 | INVOICE12 | 10,000.000 | |||
14 | 02/10/2021 | ALI13 | INVOICE13 | 20,000.000 | |||
15 | 03/10/2021 | ALI14 | INVOICE14 | 30,000.000 | |||
16 | 04/10/2021 | ALI11 | INVOICE15 | 40,000.000 | |||
17 | 05/10/2021 | ALI15 | INVOICE16 | 3,000.000 | |||
18 | 06/10/2021 | ALI14 | VOUCHER1 | 2,000.000 | |||
19 | 07/10/2021 | ALI15 | VOUCHER2 | 1,500.000 | |||
20 | 08/10/2021 | ALI16 | INVOICE17 | 30,000.000 | |||
21 | 09/10/2021 | ALI16 | VOUCHER5 | 2,000.000 | |||
22 | 10/10/2021 | ALI16 | VOUCHER6 | 1,000.00 | |||
FIRST |
the result should in second sheet . it should create the whole data with headers
deb.xlsm | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | ITEM | NAME | INVOICE NO | VOUCHER NO | DEBIT | CREDIT | BALANCE | ||
2 | 1 | ALI1 | INVOICE1,2 | - | 15,000.000 | - | 15,000.000 | ||
3 | 2 | ALI3 | INVOICE3 | - | 20,000.000 | - | 20,000.000 | ||
4 | 3 | ALI4 | INVOICE4 | - | 15,000.000 | - | 15,000.000 | ||
5 | 4 | ALI5 | INVOICE5 | - | 25,000.000 | - | 25,000.000 | ||
6 | 5 | ALI6 | INVOICE6 | - | 30,000.000 | - | 30,000.000 | ||
7 | 6 | ALI7 | INVOICE7 | - | 1,500.000 | - | 1,500.000 | ||
8 | 7 | ALI7 | INVOICE8 | - | 10,000.000 | - | 10,000.000 | ||
9 | 8 | ALI9 | INVOICE9 | - | 2,000.000 | - | 2,000.000 | ||
10 | 9 | ALI10 | INVOICE10 | - | 3,500.000 | - | 3,500.000 | ||
11 | 10 | ALI11 | INVOICE11,15 | - | 41,500.000 | - | 41,500.000 | ||
12 | 11 | ALI12 | INVOICE12 | - | 10,000.000 | - | 10,000.000 | ||
13 | 12 | ALI13 | INVOICE13 | - | 20,000.000 | - | 20,000.000 | ||
14 | 13 | ALI14 | INVOICE14 | - | 30,000.000 | 2,000.000 | 28,000.000 | ||
15 | 14 | ALI15 | INVOICE16 | VOUCHER2 | 3,000.000 | 1,500.000 | 1,500.000 | ||
16 | 15 | ALI16 | INVOICE17 | VOUCHER5,6 | 30,000.000 | 3,000.000 | 27,000.000 | ||
SECOND |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G2:G16 | G2 | =E2-F2 |