Sum, indirect, substitute, address, row problem

David Wale

New Member
Joined
Sep 17, 2012
Messages
25
Excel 2016 (Windows) 32 bit
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAM
1ARS1111111ARS1111111
2ARS1234567ARS11123334445556677
3ARS123456789101112131415161718ARS123456789101112131415161718
4ARS1311333ARS11131111113333333
5
6BLB1111111BLB1111111
7BLB1234567BLB111123344455556677
8BLB123456789101112131415161718BLB123456789101112131415161718
9BLB3000103BLB333300000011110033
10
11CHE1111111CHE1111111
12CHE1234567CHE111123333444556667
13CHE123456789101112131415161718CHE123456789101112131415161718
14CHE1101330CHE111110000111333330

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet6 (2)
Hello Forum
I have created the tables, (on the left), and have filled in the tables, (on the right), with what I wish to see.
There are two parts to this problem:-
Part one - For example, take CHE table. (on the left), B12=1, C12, D12 and E12 are blank. So, in the, (right hand table), using the CHE, (right hand table), what I wish to see is, V12, W12, X12 and Y12=1. And like wise for 2,3,4,5,6,7 etc.
Part two - For example, take CHE table. (on the left), B14=1, C14, D14 and E14 are blank. So, in the, (right hand table), using the CHE, (right hand table), what I wish to see is, V14, W14, X14 and Y14=1. And like wise for 0,1,3 etc.
Thank you
David Wale


 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,799
you can try Power Query (Get&Transform)

eg.

Column1Column2Column3Column4Column5Column6Column7Column8Column9Column10Column11Column12Column13Column14Column15Column16Column17Column18Column19
ARS
1​
1​
1​
1​
1​
1​
1​
ARS
1​
1​
1​
2​
3​
3​
3​
4​
4​
4​
5​
5​
5​
6​
6​
7​
7​
ARS
1​
2​
3​
4​
5​
6​
7​
8​
9​
10​
11​
12​
13​
14​
15​
16​
17​
18​
ARS
1​
1​
1​
3​
1​
1​
1​
1​
1​
1​
3​
3​
3​
3​
3​
3​
3​

Code:
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Transpose = Table.Transpose(Source),
    Promote = Table.PromoteHeaders(Transpose, [PromoteAllScalars=true]),
    Fill = Table.FillDown(Promote,{"ARS_1", "ARS_2", "ARS_3"}),
    Demote = Table.DemoteHeaders(Fill),
    TransposeBack = Table.Transpose(Demote),
    Extract = Table.TransformColumns(TransposeBack, {{"Column1", each Text.Start(_, 3), type text}})
in
    Extract[/SIZE]
 

David Wale

New Member
Joined
Sep 17, 2012
Messages
25
Good morning Sandy666
Thank you for supplying a solution to my, (sum, indirect, substitute, address, row problem). At the moment, I am unsure how to make this work, as I am unfamiliar with Power Query (Get&Transform), but I am reading through and printing articles, to get a better understanding.
So thank you again for your help.
David Wale
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,799
You are welcome
feel free if you have more questions
Have a nice day
 

Watch MrExcel Video

Forum statistics

Threads
1,109,450
Messages
5,528,819
Members
409,839
Latest member
akashsadhu
Top