MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Complicated Pasting for QIF format


Posted by Justin McLain on September 29, 2001 6:51 PM

I am entering in several years of financial information into quicken. My statments are in paperform and the bank does not offer them electronically. I have decided that it would be much easier to perform the data entry in Excel versus directly into Quicken.
This being the case, I have analyzed the make-up of the QIF file format and have written formulas that will malipulate my 5 column table into the appropriate QIF format. The QIF format required the data to exist as tab seperated text in a single column. The data is formated in 7 rows equaling a record (the 5 formated specially columns from my table, a 6th row that contains information derived from my 5 column table, and a "^" to deliminate records) For Example;

[Sheet1] Data Source WorkSheet
A B C D E
1 Date Amount Payee Type Method
2 9/28 $500.00 IRS Tax EFT
3 9/29 $45.00 Shell Fuel ChkCrd

Is formated as
[Sheet2] Data Result WorkSheet
A
1 !BankNum
2 D09/28' 0
3 U-500.00
4 T-500.00
5 NEFT
6 PIRS
7 LTax
8 ^
9 D09/29' 0
10 U-45.00
11 T-45.00
12 NChkCrd
13 PShell
14 LFuel
15 ^

I've tested it an works perfectly, but the pasting additional records is very tedious. Pasting advances the row numbers in my formula's by 7. Although, I am pasting (or dragging) 7 rows at a time in my results table, I want it only to advance the row number in my formulas by 1. Does anyonw know a way to paste or fill in a manner that let me paste 7 rows at a time, but only advance the row numbers in my formulas by 1? See the Examples Below to illustrate my question.

(Example Result of Paste/Fill, Rows 2-8 are copied and pasted into rows 9-22])
A
1 !BankNum
2 =CONCATENATE("D",Data!A2,"' 0")
3 =CONCATENATE("U-",Data!B2,)
4 =CONCATENATE("T-",Data!B2,)
5 =IF( LEN(Data!D2) <> 0, CONCATENATE("N",Data!D2,), CONCATENATE("N", "ChkCrd"))
6 =CONCATENATE("P",Data!C2,)
7 =CONCATENATE("L",Data!E2,)
8 ^
9 =CONCATENATE("D",Data!A9,"' 0")
10 =CONCATENATE("U-",Data!B9,)
11 =CONCATENATE("T-",Data!B9,)
12 =IF( LEN(Data!D9) <> 0, CONCATENATE("N",Data!D9,), CONCATENATE("N", "ChkCrd"))
13 =CONCATENATE("P",Data!C9,)
14 =CONCATENATE("L",Data!E9,)
15 ^
16 =CONCATENATE("D",Data!A16,"' 0")
17 =CONCATENATE("U-",Data!B16,)
18 =CONCATENATE("T-",Data!B16,)
19 =IF( LEN(Data!D16) <> 0, CONCATENATE("N",Data!D16,), CONCATENATE("N", "ChkCrd"))
20 =CONCATENATE("P",Data!C16,)
21 =CONCATENATE("L",Data!E16,)
22 ^
Continues for N Records / Rows of Source Data....

(DESIRED Results, Rows 2-8 are copied and pasted into rows 9-22])
A
1 !BankNum
2 =CONCATENATE("D",Data!A2,"' 0")
3 =CONCATENATE("U-",Data!B2,)
4 =CONCATENATE("T-",Data!B2,)
5 =IF( LEN(Data!D2) <> 0, CONCATENATE("N",Data!D2,), CONCATENATE("N", "ChkCrd"))
6 =CONCATENATE("P",Data!C2,)
7 =CONCATENATE("L",Data!E2,)
8 ^
9 =CONCATENATE("D",Data!A3,"' 0")
10 =CONCATENATE("U-",Data!B3,)
11 =CONCATENATE("T-",Data!B9,)
12 =IF( LEN(Data!D3) <> 0, CONCATENATE("N",Data!D3,), CONCATENATE("N", "ChkCrd"))
13 =CONCATENATE("P",Data!C3,)
14 =CONCATENATE("L",Data!E3,)
15 ^
16 =CONCATENATE("D",Data!A4,"' 0")
17 =CONCATENATE("U-",Data!B4,)
18 =CONCATENATE("T-",Data!B4,)
19 =IF( LEN(Data!D4) <> 0, CONCATENATE("N",Data!D4,), CONCATENATE("N", "ChkCrd"))
20 =CONCATENATE("P",Data!C4,)
21 =CONCATENATE("L",Data!E4,)
22 ^
Continues for N Records / Rows of Source Data....

Thank you.

Regards,
Justin


Posted by Justin R. McLain on September 29, 2001 7:01 PM

Please note that row 11 of the Desired results should be as follows;

11 =CONCATENATE("T-",Data!B3,)

Thanks,
Justin

Posted by Debra Dalgleish on October 01, 2001 5:36 AM

On your Results sheet, you could use the following formulas in cells
A2:A8, then copy and paste into the rows below, as you need them.

="D"&INDIRECT("Data!A"&INT(ROW()/7+2))&"' 0"
="U-"&INDIRECT("Data!B"&INT(ROW()/7+2))
="T-"&INDIRECT("Data!B"&INT(ROW()/7+2))
=IF(LEN(INDIRECT("Data!D"&INT(ROW()/7+2)))<>0,"N"&INDIRECT("Data!D"&INT(ROW()/7+2)),"NChkCrd")
="P"&INDIRECT("Data!C"&INT(ROW()/7+2))
="L"&INDIRECT("Data!E"&INT(ROW()/7+1))
^

Posted by Justin McLain on October 01, 2001 5:36 AM

Worked Perfectly. Thanks!