new user - multiple lines

[karenTC]

I am trying to capture information that, for the most part is on one line. However, some records have 2 lines. The problem is the consistency of the second line. The first field of the first line always has an account number. However the second line, the only consistency is the account field is blank. Other than that, the information may be in any of the other fields, but other wise is blank.

The columns all line up exactly. I've tried as separate lines with the second being the detail and the first being the append, but it only captures records with 2 lines. If I reverse the append and detail, the second line is attached to the next record.

173737 Smith, MAXIN 105.23 12.30 .00 .00 .00 85.28
7.38
173741 Smith, MARY 77.83 21.73 .00 .00 .00 53.07
173742 Smith, JOHN 81.14 21.78 .00 .00 .00 57.13
173743 Harold, ED 17.07 20.05 17.40 .00 .00 13.42
173745 Barnes, GAIL 17.07 20.05 17.40 .00 .00 13.42
173743 Gamble, Bill 130.43 21.30 .00 .00 .00 137.13
173747 Bramble, FLORENCE 21.23 .00 .00 .00 .00 21.23
173748 Markham, GRADY 21.82 17.58 .00 .00 .00 2.24
173747 Belfry, IRENE .00 18.85 128.88 .00 .00 110.03
DISCOUNTS: .00 7.30 28.87 17.27

[Data Kruncher]

Hi Karen. Welcome to the forum.

Is the 7.38 value for the 173737 record actually in its own line in the data file, or is that just a formatting issue in this post?

I guess I'm really asking if the DISCOUNTS item is the only 2nd line item that you want to tack on to the 173747 Irene Belfrey record, or if the 7.38 is one too. We can probably handle either case; I'd just like to clarify before we get going.

[karenTC]

Thanks for the quick response. The 7.38 is actually farther over on the second line. The second line ALWAYS begins with blanks where normally there would be an account number. After that, it MAY hold the word "DISCOUNTS" or it might just be a lone number or two numbers such as the 7:38. If every second line contained the word "DISCOUNTS", it would be easier. But really, the only common denominator is the blanks where there would be an account number if it were a new record. The second line contains additional information relevant to the account, when it pertains, which isn't frequently. What I can say is that all number line up exactly.

What I'd like to be able to do is begin a new record when there is a value (non-blank) in the first column.

Monarch is fabulous, .... when I can get it to work. ANd I know there is a lot for me to understand yet.

[Data Kruncher]

OK, in order to extract this properly, you'll need to employ two append templates. Specifically, you'll need two "guru trap" (http://excelwithmonarch.com/extract/experimentation-produces-monarch-success) templates. I'd recommend that you spend a couple of minutes reading the linked item before preceding below. It'll help to make sense of this.

Keeping things simple with your example, I built a detail template which had a single N in column 1, using a one line sample. Paint the field(s) as you would normally.

Now select the Discounts line as a sample, and for the trap, again use a single N in column 1 for the trap and paint the discount values as necessary. For each of these fields, on the Advanced tab specify that the word discount appears on the same line. Save the template.

Repeat this process for the 7.38 line, except that this time you'll specify that 4 spaces appear in the same line using the Advanced field properties.

HTH,
Kruncher

[karenTC]

Thank you SOOOO much. I shall take all this information and work on it. I was reading about the guru trap yesterday and wondered if it would help. Again, I really appreciate your quick response.

[karenTC]

Done! Thank you so much. I'm still trying to understand why it worked, but it worked. I can think of half a dozen other reports I'd given up on that this will work for. You've opened a whole new opportunity and I thank you.

[Data Kruncher]

Excellent news.

Welcome to the initial signs of a growing and lengthy Monarch addiction. The more you play and the more that you learn, the more that you realize that there is left to learn and the more that you can do with Monarch.

It's a vicious circle. Isn't it great? :D

[karenTC]

You've nailed it. I have a ton of other work that needs attention, and all I would prefer is to revisit those other reports. I'm hooked.:eek:

[Data Kruncher]

Yup, you've got it bad then. Your co-workers aren't ready for what's coming. You're about to excel with Monarch. ;)