formula required

[manojk71273]

Hi All

I have Data in following fashion

Columns A B C D
1 0 2 0
0 1 0 2
0 0 1 2
0 0 0 1

you can get the combinations from the above table. (the figures represents the amounts)

Now I want to take the data in another sheet by ignoring the zeros in the following way

A B
abcd 1 (FIGURE FROM A1)
xyz 2 (FIGURE FROM C1)

A B
abcd 1 (FIGURE FROM B1)
xyz 2 (FIGURE FROM D1)

SO ON & SO FORTH

how can i do the above thing

Thanks in advance to all

regards
manoj

 

[Mel Smith]

Hi Manoj,

Have you tried using an "IF" formula. Something along the lines of:

(On sheet 2 in column A, and so on): =IF(Sheet1!A1>0,Sheet1!A1,"")

This will display all values greater than 0 or else display a blank cell.

Mel

 

[manojk71273]

Hi mel

I dont want to consider the total values of column having zero or blank value

means if column a1 is haveing zero or nul value the sheet2!a1 should start from b1 whos having some value so on

regards
manoj

 

[hsk]

One way ..... meight be lenthy but just try ....
Assuming there are only 4 columns .....
On sheet1 in column E concatenate all the columns that are not 0

E1 => =CONCATENATE(IF(OR(ISBLANK(A1),A1=0),",",A1),",",IF(OR(ISBLANK(B1),B1=0),",",B1),",",IF(OR(ISBLANK(C1),C1=0),",",C1),",",IF(OR(ISBLANK(D1),D1=0),",",D1))

For ur e.g. value u will get in E1 will be 1,,2,,

Copy -> Paste special - Value -> Sheet2

Then delimit the data on comma ( check "Treat Consecutive delimiters as one)

 

[Scott Huish]

What do the abcd and xyz for? Are there other letter combinations?

 

[manojk71273]

Hi hotpepper & others
ABCD or XYZ are codes for taxes to be paid to the govt.

there are many code for different services

regards,
manoj

 

[manojk71273]

Hi Hsk

your formula doesnt give the answer that i required

I clarify my question once again

say amounts are in columns A1=st B1=edc C1=hedc & D1=int/pen

now if i want to pay only edc having amt rs. 5

then in sheet 2 at A2 I should see only EDC code & nothing else (so the formula should look for non blanks along the a1 to d1 & produce it in sheet 2 (only non blanks)

thanks
manoj

 

[manojk71273]

Book2
	A	B	C	D	E	F
8	ST	EDC	HEDC	Int/Pen
9	0	12	0	5	17
10
11
12
13	It should be look like this in Sheet 2
14
15	EDC	12
16	Int/Pen	5
17		17
18
Sheet1

 

[Krishnakumar]

Hi,

In B2 on sheet2

=HLOOKUP(A2,Sheet1!$A$8:$D$9,2,0)

where A2 houses EDC

HTH

 

[manojk71273]

HI Krishnakumar

your formula works. but what i want to do is if

ST is 2 EDC is 1 & hedc is 0 then,

only ST 2 & edc 1 should be shown a15 & a16
if I want to pay only Int/Pen then,

only Int/pen should be shown in a15 and the amount in b15

thanks
manoj