Need Advice on how I should approach a schedule K-1 for QC

shyy · Apr 22, 2014

Hey guys,

I finally found a program that can out put the amounts from a schedule k-1 form. Now comes the hard part, adding logical formulas.

Some of the line items either have an amount to or not. What the pdf2xl program does is output where if the line item has an amount to it, it would add the amount right below the line item. If it doesn't have an amount, it would just add the next line item underneath. Question is what is a good approach to get this to automate, so if line items have an amount, then that amount is known to be line item cell above. Here comes the tricky part as well, some line items have alphabets to them, so a particular line item say 11 would have A-F or line 13 would have A-W. What is a good way to have the line item output the line item + alphabet?

Column A & C = Line Item
Column B & D = Alphabet

Link to the IRS form http://www.irs.gov/pub/irs-pdf/f1065sk1.pdf

Thanks in advance

Excel 2010

	A	B
Ordinary business income (loss)	Credits
Net rental real estate income (loss)
Other net rental income (loss)	Foreign transactions
A	OC
Guaranteed payments
B
Interest income
H
6a	Ordinary dividends
6b	Qualified dividends
Royalties
Net short-term capital gain (loss)
9a	Net long-term capital gain (loss)	Alternative minimum tax (AMT) items
*
9b	Collectibles (28%) gain (loss)
9c	Unrecaptured section 1250 gain
Net section 1231 gain (loss)	Tax-exempt income and
nondeductible expenses
Other income (loss)
F*
Distributions
Section 179 deduction
Other deductions
K*	Other information
W*	B
Self-employment earnings (loss)
*See attached statement for additional information.

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]15[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]16[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]4159[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]923663[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]17[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]-923663[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]18[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]4159[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]19[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]

[TD="align: right"]13718[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]25[/TD]

[TD="align: right"]5854[/TD]

[TD="align: right"]13718[/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1

Desired End Result:
Excel 2010

	G	H
Ordinary business income (loss)	Credits
Net rental real estate income (loss)
Other net rental income (loss)	Foreign transactions
A	OC
Guaranteed payments
B
Interest income
H
6a	Ordinary dividends
6b	Qualified dividends
Royalties
Net short-term capital gain (loss)
9a	Net long-term capital gain (loss)	Alternative minimum tax (AMT) items
*
9b	Collectibles (28%) gain (loss)
9c	Unrecaptured section 1250 gain
Net section 1231 gain (loss)	Tax-exempt income and
nondeductible expenses
Other income (loss)
F*
Distributions
Section 179 deduction
Other deductions
K*	Other information
W*	B
Self-employment earnings (loss)
*See attached statement for additional information.

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]15[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]16[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #00B0F0"]16B[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #00B0F0"]16H[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]17[/TD]

[TD="align: center"]14[/TD]

[TD="bgcolor: #00B0F0"]9a*[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]18[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="bgcolor: #00B0F0"]11F*[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]19[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]

[TD="bgcolor: #00B0F0"]13K*[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]25[/TD]

[TD="bgcolor: #00B0F0"]13W*[/TD]

[TD="bgcolor: #00B0F0"]20B[/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1

shyy · Apr 22, 2014

Example where I am having trouble and what I am currently doing:

Some of these line items have 1 - 3 alphabets to them, in this case just 2 for line 13.

Excel 2010

	A	B
Other deductions	Problem	Desired Result
K*	13K*	13K*
W*	KW	13W*

<tbody>
[TD="align: center"]23[/TD]
[TD="align: right"]13[/TD]

[TD="align: center"]24[/TD]

[TD="align: right"]13718[/TD]

[TD="align: center"]25[/TD]

[TD="align: right"]5854[/TD]

</tbody>

Sheet1

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C24[/TH]
[TD="align: left"]=IF(ISNUMBER(B24)=TRUE,A23&A24,"")[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C25[/TH]
[TD="align: left"]=IF(ISNUMBER(B25)=TRUE,A24&A25,"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

shyy · Apr 22, 2014

Getting close, now having trouble if the line items are just by themselves.

=IF(ISNUMBER(J48)=TRUE,I48&IF(ISNUMBER(I48),I47,IF(ISNUMBER(I47)=TRUE,I47,I46)),"")

Excel 2010

A

B

E

23

13

Other deductions

24

K*

13718

K*13

25

W*

5854

W*13

Sheet1

Cell Formulas
Range	Formula
E23	=IF(ISNUMBER(B23)=TRUE,A23&IF(ISNUMBER(A23),A22,IF(ISNUMBER(A22)=TRUE,A22,A21)),"")
E24	=IF(ISNUMBER(B24)=TRUE,A24&IF(ISNUMBER(A24),A23,IF(ISNUMBER(A23)=TRUE,A23,A22)),"")
E25	=IF(ISNUMBER(B25)=TRUE,A25&IF(ISNUMBER(A25),A24,IF(ISNUMBER(A24)=TRUE,A24,A23)),"")

shyy · Apr 22, 2014

Getting there..

Excel 2010

	I	J	K
Ordinary business income (loss)		Credits
Net rental real estate income (loss)
Other net rental income (loss)		Foreign transactions
	A	OC
Guaranteed payments
	B	AB
Interest income
	H	BH
6a	Ordinary dividends
6b	Qualified dividends
Royalties
Net short-term capital gain (loss)
Net long-term capital gain (loss)		Alternative minimum tax (AMT) items
*	9.1*
9b	Collectibles (28%) gain (loss)
9c	Unrecaptured section 1250 gain
Net section 1231 gain (loss)		Tax-exempt income and
	nondeductible expenses
Other income (loss)
F*	11F*
	Distributions
Section 179 deduction		A	19A
Other deductions
K*	13K*	Other information
W*	13W*	B	20B
Self-employment earnings (loss)
A	14A
B	14B
E	14E

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]389[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]15[/TD]

[TD="align: center"]390[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]391[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]16[/TD]

[TD="align: center"]392[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]393[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]394[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]467[/TD]

[TD="align: center"]395[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]396[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]12851[/TD]

[TD="align: center"]397[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]398[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]399[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]400[/TD]
[TD="align: right"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]401[/TD]
[TD="align: right"]9.1[/TD]

[TD="align: right"]17[/TD]

[TD="align: center"]402[/TD]

[TD="align: right"]-12851[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]403[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]404[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]405[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]18[/TD]

[TD="align: center"]406[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]407[/TD]
[TD="align: right"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]408[/TD]

[TD="align: right"]467[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]409[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]19[/TD]

[TD="align: center"]410[/TD]
[TD="align: right"]12[/TD]

[TD="align: right"]35029[/TD]

[TD="align: center"]411[/TD]
[TD="align: right"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]412[/TD]

[TD="align: right"]3822[/TD]

[TD="align: right"]20[/TD]

[TD="align: center"]413[/TD]

[TD="align: right"]657[/TD]

[TD="align: right"]3822[/TD]

[TD="align: center"]414[/TD]
[TD="align: right"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]415[/TD]

[TD="align: right"]50[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]416[/TD]

[TD="align: right"]44[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]417[/TD]

[TD="align: right"]23[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]K389[/TH]
[TD="align: left"]=IF(COUNT(J387:J389)=3,I386&I389,IF(ISNUMBER(J389)=TRUE,IF(ISNUMBER(I389),I388,IF(ISNUMBER(I388)=TRUE,I388,I387)&I389),IF(COUNT(J387:J389)=3,I386&I389,"")))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]K390[/TH]
[TD="align: left"]=IF(COUNT(J388:J390)=3,I387&I390,IF(ISNUMBER(J390)=TRUE,IF(ISNUMBER(I390),I389,IF(ISNUMBER(I389)=TRUE,I389,I388)&I390),IF(COUNT(J388:J390)=3,I387&I390,"")))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]K391[/TH]
[TD="align: left"]=IF(COUNT(J389:J391)=3,I388&I391,IF(ISNUMBER(J391)=TRUE,IF(ISNUMBER(I391),I390,IF(ISNUMBER(I390)=TRUE,I390,I389)&I391),IF(COUNT(J389:J391)=3,I388&I391,"")))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]K392[/TH]
[TD="align: left"]=IF(COUNT(J390:J392)=3,I389&I392,IF(ISNUMBER(J392)=TRUE,IF(ISNUMBER(I392),I391,IF(ISNUMBER(I391)=TRUE,I391,I390)&I392),IF(COUNT(J390:J392)=3,I389&I392,"")))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]K393[/TH]
[TD="align: left"]=IF(COUNT(J391:J393)=3,I390&I393,IF(ISNUMBER(J393)=TRUE,IF(ISNUMBER(I393),I392,IF(ISNUMBER(I392)=TRUE,I392,I391)&I393),IF(COUNT(J391:J393)=3,I390&I393,"")))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]K394[/TH]
[TD="align: left"]=IF(COUNT(J392:J394)=3,I391&I394,IF(ISNUMBER(J394)=TRUE,IF(ISNUMBER(I394),I393,IF(ISNUMBER(I393)=TRUE,I393,I392)&I394),IF(COUNT(J392:J394)=3,I391&I394,"")))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]K395[/TH]
[TD="align: left"]=IF(COUNT(J393:J395)=3,I392&I395,IF(ISNUMBER(J395)=TRUE,IF(ISNUMBER(I395),I394,IF(ISNUMBER(I394)=TRUE,I394,I393)&I395),IF(COUNT(J393:J395)=3,I392&I395,"")))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]K396[/TH]
[TD="align: left"]=IF(COUNT(J394:J396)=3,I393&I396,IF(ISNUMBER(J396)=TRUE,IF(ISNUMBER(I396),I395,IF(ISNUMBER(I395)=TRUE,I395,I394)&I396),IF(COUNT(J394:J396)=3,I393&I396,"")))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]K397[/TH]
[TD="align: left"]=IF(COUNT(J395:J397)=3,I394&I397,IF(ISNUMBER(J397)=TRUE,IF(ISNUMBER(I397),I396,IF(ISNUMBER(I396)=TRUE,I396,I395)&I397),IF(COUNT(J395:J397)=3,I394&I397,"")))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]K398[/TH]
[TD="align: left"]=IF(COUNT(J396:J398)=3,I395&I398,IF(ISNUMBER(J398)=TRUE,IF(ISNUMBER(I398),I397,IF(ISNUMBER(I397)=TRUE,I397,I396)&I398),IF(COUNT(J396:J398)=3,I395&I398,"")))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"][/TH]
[TD="align: left"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

shyy · Apr 25, 2014

Incase anyone is wondering how I am doing it, seems to work

Sample Data:

Excel 2010

A

B

C

D

63

Table 6

64

Part III Partner's Share of Current Year Income, Deductions, Credits, and Other Items

65

1

Ordinary business income (loss)

15

Credits

66

2

Net rental real estate income (loss)

67

3

Other net rental income (loss)

16A

Foreign transactions OC

68

4

Guaranteed payments

B

31

69

5

Interest income

H

3,278.00

70

6a

Ordinary dividends

71

6b

Qualified dividends

72

7

Royalties

73

8

Net short-term capital gain (loss)

74

9a *

Net long-term capital gain (loss) -3,278.

17

Alternative minimum tax (AMT) items

75

9b

Collectibles (28%) gain (loss)

76

9c

Unrecaptured section 1250 gain

77

10

Net section 1231 gain (loss)

18

Tax-exempt income and nondeductible expenses

78

11 F*

Other income (loss) 31.

79

80

19 A

Distributions 1,570.

81

12

Section 179 deduction

82

83

13 K*

Other deductions 154.

84

85

20B

Other information 154.

86

W*

43

87

14

Self-employment earnings (loss)

Sheet1

Excel 2010

	F	G

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]65[/TD]

[TD="align: center"]66[/TD]

[TD="align: center"]67[/TD]

[TD="align: center"]68[/TD]

[TD="align: right"] 31 [/TD]

[TD="align: center"]69[/TD]

[TD="align: right"] 3,278 [/TD]

[TD="align: center"]70[/TD]

[TD="align: center"]71[/TD]

[TD="align: center"]72[/TD]

[TD="align: center"]73[/TD]

[TD="align: center"]74[/TD]
[TD="align: right"] (3,278)[/TD]

[TD="align: center"]75[/TD]

[TD="align: center"]76[/TD]

[TD="align: center"]77[/TD]

[TD="align: center"]78[/TD]
[TD="align: right"] 31 [/TD]

[TD="align: center"]79[/TD]

[TD="align: center"]80[/TD]

[TD="align: right"] 1,570 [/TD]

[TD="align: center"]81[/TD]

[TD="align: center"]82[/TD]

[TD="align: center"]83[/TD]
[TD="align: right"] 154 [/TD]

[TD="align: center"]84[/TD]

[TD="align: center"]85[/TD]

[TD="align: right"] 154 [/TD]

[TD="align: center"]86[/TD]
[TD="align: right"] 43 [/TD]

[TD="align: center"]87[/TD]

</tbody>

Sheet1

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F65[/TH]
[TD="align: left"]=IF(A65=$E$1,"",IF(OR(COUNTIF(A65,"*"&"Beginning capital"&"*"),COUNTIF(A65,"*"&"Capital contributed"&"*"),COUNTIF(A65,"*"&"Current year increase"&"*"),COUNTIF(A65,"*"&"Withdrawals"&"*"),COUNTIF(A65,"*"&"Ending capital"&"*"))=TRUE,"",IFERROR(0+TRIM(RIGHT(SUBSTITUTE(B65," ",REPT(" ",200)),200)),"")))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G65[/TH]
[TD="align: left"]=IFERROR(0+TRIM(RIGHT(SUBSTITUTE(D65," ",REPT(" ",200)),200)),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F66[/TH]
[TD="align: left"]=IF(A66=$E$1,"",IF(OR(COUNTIF(A66,"*"&"Beginning capital"&"*"),COUNTIF(A66,"*"&"Capital contributed"&"*"),COUNTIF(A66,"*"&"Current year increase"&"*"),COUNTIF(A66,"*"&"Withdrawals"&"*"),COUNTIF(A66,"*"&"Ending capital"&"*"))=TRUE,"",IFERROR(0+TRIM(RIGHT(SUBSTITUTE(B66," ",REPT(" ",200)),200)),"")))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G66[/TH]
[TD="align: left"]=IFERROR(0+TRIM(RIGHT(SUBSTITUTE(D66," ",REPT(" ",200)),200)),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"][/TH]
[TD="align: left"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

markmzz · Apr 25, 2014

Hi Shyy,

If I understand correctly what you want, maybe this can helps:

Layout

[TABLE="width: 467"]
<tbody>[TR]
[TD="width: 137, bgcolor: transparent"]Col01[/TD]
[TD="width: 176, bgcolor: transparent"]Col02[/TD]
[TD="width: 41, bgcolor: transparent"]Col03[/TD]
[TD="width: 41, bgcolor: transparent"]Col04[/TD]
[TD="width: 185, bgcolor: transparent"]Col05[/TD]
[TD="width: 41, bgcolor: transparent"]Col06[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"]Ordinary business income (loss)[/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: transparent, align: right"]15[/TD]
[TD="bgcolor: transparent"]Credits[/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"]Net rental real estate income (loss)[/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"]Other net rental income (loss)[/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: transparent, align: right"]16[/TD]
[TD="bgcolor: transparent"]Foreign transactions[/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: transparent"]OC[/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"]Guaranteed payments[/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: transparent"]B[/TD]
[TD="bgcolor: transparent, align: right"]4159[/TD]
[TD="bgcolor: yellow"]16B[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"]Interest income[/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: transparent"]H[/TD]
[TD="bgcolor: transparent, align: right"]923663[/TD]
[TD="bgcolor: yellow"]16H[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]6a[/TD]
[TD="bgcolor: transparent"]Ordinary dividends[/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]6b[/TD]
[TD="bgcolor: transparent"]Qualified dividends[/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent"]Royalties[/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent"]Net short-term capital gain (loss)[/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]9a[/TD]
[TD="bgcolor: transparent"]Net long-term capital gain (loss)[/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: transparent, align: right"]17[/TD]
[TD="bgcolor: transparent"]Alternative minimum tax (AMT) items[/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]*[/TD]
[TD="bgcolor: transparent, align: right"]-923663[/TD]
[TD="bgcolor: yellow"]9a*[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]9b[/TD]
[TD="bgcolor: transparent"]Collectibles (28%) gain (loss)[/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]9c[/TD]
[TD="bgcolor: transparent"]Unrecaptured section 1250 gain[/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent"]Net section 1231 gain (loss)[/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: transparent, align: right"]18[/TD]
[TD="bgcolor: transparent"]Tax-exempt income and[/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]nondeductible expenses[/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent"]Other income (loss)[/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]F*[/TD]
[TD="bgcolor: transparent, align: right"]4159[/TD]
[TD="bgcolor: yellow"]11F*[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: transparent, align: right"]19[/TD]
[TD="bgcolor: transparent"]Distributions[/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12[/TD]
[TD="bgcolor: transparent"]Section 179 deduction[/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]13[/TD]
[TD="bgcolor: transparent"]Other deductions[/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]K*[/TD]
[TD="bgcolor: transparent, align: right"]13718[/TD]
[TD="bgcolor: yellow"]13K*[/TD]
[TD="bgcolor: transparent, align: right"]20[/TD]
[TD="bgcolor: transparent"]Other information[/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]W*[/TD]
[TD="bgcolor: transparent, align: right"]5854[/TD]
[TD="bgcolor: yellow"]13W*[/TD]
[TD="bgcolor: transparent"]B[/TD]
[TD="bgcolor: transparent, align: right"]13718[/TD]
[TD="bgcolor: yellow"]20B[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]14[/TD]
[TD="bgcolor: transparent"]Self-employment earnings (loss)[/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]*See attached statement for additional information.[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]*************************[/TD]
[TD="bgcolor: transparent"]*********************************[/TD]
[TD="bgcolor: transparent"]*******[/TD]
[TD="bgcolor: transparent"]*******[/TD]
[TD="bgcolor: transparent"]************************************[/TD]
[TD="bgcolor: transparent"]********[/TD]
[/TR]
</tbody>[/TABLE]

Formula

Code:

In C2 - use Ctrl+Shift+Enter to enter the formula

=IF(ISNUMBER(B2),LOOKUP(99^99,IF(CODE(RIGHT(A$1:A1,1))>57,--LEFT(A$1:A1,LEN(A$1:A1)-1),A$1:A1),A$1:A1)&A2,"")

Markmzz

shyy · Apr 25, 2014

Wow Thanks Markmzz!

When you get a chance can you explain how that formula works?

I had to use couple helper columns to get what I need, unfortunately this will not work with my sample data since I have bunch of other things that are not line item related below this table. Sorry I should have posted more of the sample.

markmzz · Apr 26, 2014

shyy said:
Wow Thanks Markmzz!

When you get a chance can you explain how that formula works?

I had to use couple helper columns to get what I need, unfortunately this will not work with my sample data since I have bunch of other things that are not line item related below this table. Sorry I should have posted more of the sample.

You are welcome.

The formula below:

LOOKUP(99^99,IF(CODE(RIGHT(A$1:A1,1))>57,--LEFT(A$1:A1,LEN(A$1:A1)-1),A$1:A1),A$1:A1)

work like this:

1) IF(CODE(RIGHT(A$1:A1,1))>57,--LEFT(A$1:A1,LEN(A$1:A1)-1),A$1:A1) - verify if the last caracter of the string isn't a number (code(0)=48 and code(9)=57)).

If it isn't, then get the first caracters of the string minus the last one ("9b" - "9", "11f" - "11") and change they to number (--).

If it is a number, then get all data (9 - 9, 11 - 11).

2) Finally, LOOKUP(99^99,IF(CODE(RIGHT(A$1:A1,1))>57,--LEFT(A$1:A1,LEN(A$1:A1)-1),A$1:A1),A$1:A1)

The LOOKUP function lookup for the last number in the result range of the part of the formula above and get the value of the range A$1:A1.

I hope that the information above helps.

Markmzz

shyy · Apr 30, 2014

Thanks for the explanation!

markmzz · Apr 30, 2014

shyy said:
Thanks for the explanation!

You are welcome.

Markmzz

Need Advice on how I should approach a schedule K-1 for QC

shyy

Well-known Member

shyy

Well-known Member

shyy

Well-known Member

shyy

Well-known Member

shyy

Well-known Member

markmzz

MrExcel MVP

shyy

Well-known Member

markmzz

MrExcel MVP

shyy

Well-known Member

markmzz

MrExcel MVP

Similar threads

Share this page

Need Advice on how I should approach a schedule K-1 for QC

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock