formula required

manojk71273

Board Regular
Joined
Apr 28, 2006
Messages
103
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
 

Excel Facts

Add Bullets to Range
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
Joined
Dec 13, 2005
Messages
952
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 28, 2006
Messages
103
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
Joined
Oct 19, 2006
Messages
567
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
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
What do the abcd and xyz for? Are there other letter combinations?
 

manojk71273

Board Regular
Joined
Apr 28, 2006
Messages
103
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
Joined
Apr 28, 2006
Messages
103
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

Board Regular
Joined
Apr 28, 2006
Messages
103
Book2
ABCDEF
8STEDCHEDCInt/Pen
90120517
10
11
12
13It should be look like this in Sheet 2
14
15EDC12
16Int/Pen5
1717
18
Sheet1
 

manojk71273

Board Regular
Joined
Apr 28, 2006
Messages
103
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
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top