# formula required

#### manojk71273

##### Board Regular
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

regards
manoj

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

#### Mel Smith

##### Well-known Member
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

##### Board Regular
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

##### Well-known Member
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

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

#### manojk71273

##### Board Regular
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

##### Board Regular
Hi Hsk

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

##### Board Regular
Book2
ABCDEF
8STEDCHEDCInt/Pen
90120517
10
11
12
13It should be look like this in Sheet 2
14
15EDC12
16Int/Pen5
1717
18
Sheet1

#### Krishnakumar

##### Well-known Member
Hi,

In B2 on sheet2

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

where A2 houses EDC

HTH

#### manojk71273

##### Board Regular
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

Replies
4
Views
120
Replies
0
Views
216
Replies
0
Views
221
Replies
12
Views
818
Replies
7
Views
428

1,181,399
Messages
5,929,743
Members
436,687
Latest member
Glass of Gin

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back