• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

ARRANGEMENT

A

AVNSH22

New Member
datatable is like this:-


DAY1 DAY2 DAY3 DAY4 DAY5 DAY6

A 3 0 0 0 0 0

B 17 0 0 0 0 0

B 4 1 0 0 0 0

C 0 1 0 0 0 0

D 0 22 5 0 0 0

A 0 0 3 0 0 0

F 0 0 2 0 0 0

G 0 0 3 0 0 0


PROBLEM IS

TO ARRANGE NON ZERO VALUES IN ONE COLUMN DAY WISE WITH THEIR ALPHABETS i.e

1 A 3

1 B 17

1 B 4

2 B 1

2 C 1

2 D 22

3 D 5

3 A 3

3 F 2

3 G 3

BLANK BLANK BLANK


AND SO ON..........

AND THERE SHOULD BE SCOPE FOR FURTHER ARRANGEMENT OF DATA I.E. FORMULA WILL BE IN THE WORKSHEET BUT IT SHOULD SHOW BLANK IF THERE IS NO FURTHER DATA .
 
Hi Avnsh22...


Thanks for your question. Before replying let me suggest you to not use the CAPS LOCK key. IFYOUTYPEEVERYTHINGINCAPS IT IS VERY DIFFICULT TO READ!


I tried to come up with a formula solution for this, but found it to be too complicated. So I wrote a small macro. See this file.


http://img.chandoo.org/playground/restruct-data-avnsh.xlsm


The logic of macro is simple.


For each of the values in the range we just see if it is not zero.

Then, we copy that value to output range and assign correct day & item codes to it.
 
Hello Avnsh22,

Here is one approach to producing the output using formulas:


The sample data:

[pre]
Code: 
Names	Day1	Day2	Day3	Day4	Day5	Day6
A	3	0	0	0	0	0
B	17	0	0	0	0	0
B	4	1	0	0	0	0
C	0	1	0	0	0	0
D	0	22	5	0	0	0
A	0	0	3	0	0	0
F	0	0	2	0	0	0
G	0	0	3	0	0	0
For readability, I added the following Excel Names:

I named the list of names in col A as "Names"

I named the day headers (in row 1) as "DaysHeader"

I named the raw numbers as "RawDaysData"


To keep things simple, I also use a Named formula called "PositionOfDayValuesWithoutZeros"

=SMALL(IF(RawDaysData>0, (ROW(RawDaysData)-ROW(INDEX(RawDaysData,1,1))+1) + (COLUMN(RawDaysData)-COLUMN(INDEX(RawDaysData,1,1))+1)%), ROW(Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTIF(RawDaysData, ">0"))))


(Basically, it creates an array containing the row and column info for the non-zero values.)


For simplicity, I also use a helper column (labeled "index" in the sample output), with the following formula in the first output row (in cell D13):

=INDEX(PositionOfDayValuesWithoutZeros, ROW(1:1))


Now that the "index" (helper) column has the row and col info, we can simply refer to the "Names", "DaysHeader" and "RawDaysData" arrays to get the desired data.

Day	Name	Value	Index
Day1	A	3	1.01
Day1	B	17	2.01
Day1	B	4	3.01
Day2	B	1	3.02
Day2	C	1	4.02
Day2	D	22	5.02
Day3	D	5	5.03
Day3	A	3	6.03
Day3	F	2	7.03
Day3	G	3	8.03
[/pre]
In the above example, the "Day" column is calculated as

=INDEX(DaysHeader,1,MOD(D13,1)*10^2)


The "Name" column is calculated as

=INDEX(Names,INT(D13))


The "Value" column is calculated as

=INDEX(RawDaysData, INT(D13), MOD(D13,1)*10^2)


Cheers,

Sajan.
 
i do not know macro please do create a macro as like i want in my excel sheet.

here is the link

http://speedy.sh/JH72Y/help.xlsx
 
lalit232


You have put your post in to someone else and by doing this you will limit to number of people who can help as they may not open this post.


Start a new post.
 
Hi Lalit ,


I have downloaded your file , but am not able to understand what you want.


Just for instance , row 42 shows the result for SUPERLITE AF GP 150 , which has only one entry against day 2 ; what about the entry against day 3 ? Is this also to appear in the results ? If so , how , since in the result area , you are having two columns together for value and weight.


Narayan
 
Hi Lalit ,


I am sorry , but you need to be more detailed in your answers , otherwise I cannot try and think of a solution. I hope someone else can help you.


Narayan
 
lalit232


No one was asking you to show data just to start your own post and not hijack some one else's, the answers to different questions would confuse the OP. By having your own post others who have not viewed this post could view and maybe help.
 
datatable is like this:-


DAY1 DAY2 DAY3 DAY4 DAY5 DAY6

A r 3 0 0 0 0 0

B p 17 0 0 0 0 0

B k 4 1 0 0 0 0

C g 0 1 0 0 0 0

D g 0 22 5 0 0 0

A h 0 0 3 0 0 0

F i 0 0 2 0 0 0

G e 0 0 3 0 0 0


PROBLEM IS

TO ARRANGE NON ZERO VALUES IN ONE COLUMN DAY WISE WITH THEIR ALPHABETS i.e

1 A r 3

1 B p 17

1 B k 4

2 B k 1

2 C g 1

2 D g 22

3 D g 5

3 A h 3

3 F i 2

3 G e 3

BLANK BLANK BLANK


AND SO ON..........

AND THERE SHOULD BE SCOPE FOR FURTHER ARRANGEMENT OF DATA I.E. FORMULA WILL BE IN THE WORKSHEET BUT IT SHOULD SHOW BLANK IF THERE IS NO FURTHER DATA .


if it can be done by formulas not by macro then it would be good
 
Ok I give in, I guess "start your own post and not hijack some one else's" does not translate too well
 
You must log in or register to reply here.
Back
Top