Trying to insert formulas if & only if Value is not 0

[Jaybird]

Hi everyone,

This is a recurring problem that's been bugging me for a while (well, 1.5 problems). I have a list of about 200 values that vary from anywhere from 1-15 entries per value. To clarify let's say it looks like this:

1) Problem 1: Assume you have this dataset:

A 3
A 2
A 6
B 1
C 9
C 11
C 4
D 8
etc.

Now I'm trying to create separate worksheets for each of these, where it would show:

A 3
A 2
A 6

on one, then afterwards

B 1

on the other etc.

I have been using vlookup tables, but the problem is since I don't know how many formulas I use per page, I always end up with a string of 0s afterwards. So the data ends up looking like:

A 3
A 2
A 6
0 0
0 0
0 0

and

B 1
0 0
0 0

etc.

Having to manually fill-down the exact amount of entries on each one is out of the question because I have hundreds of entries for each and this needs to be done every week. Since this is a recurring, random data-set though, I have to include the maximum amount of =vlookup formulas to make sure they're all included in the separate reports.

Anyone know how I can make it so the values are included If and only if they do not equal 0, otherwise it's a blank space?


(Problem 2) From the raw data, i want to create a final set of the sum of each of these, without any space in between. so for example:

A 2
A 1
A 9
C 1
F 2
etc.

So notice there is no B, D or E. However since I'm using lookup tables, I end up with this Summary Chart:

A 12
B 0
C 1
D 0
E 0
F 2

It looks very sloppy with all of those empty 0s. Any way I can clean it up and make it so that only the letters with actual values appear, without any spaces in between?:

A 12
C 1
F 2

Thanks so much in advance!

 

[Jaybird]

Wow, thanks for the macro, but honestly I think it's a bit out of my league when it comes to MS Excel skills to adapt it to what I need. I think all that is needed is some sort of Index/Offset/Match function, but I can't for the life of me figure out the correct syntax.

Maybe I can clear the question up some. I'm looking to transform an arbitrary list:

Input Worksheet:

John 125
John 673
Amy 112
Paul 743
John 210
Paul 1100
Amy 750


Into separate worksheets, which would look like this:

John's Worksheet:
John 125
John 673
John 210

Amy's Worksheet:
Amy 112
Amy 750

Paul's Worksheet:
Paul 743
Paul 1100

Without any spaces in between (so John's worksheet would have the john numbers on 1, 2, and 3.)

I think I have it almost figured out, where I would index the Input worksheet and somehow match the first John, the second John and the 3rd John using the small function, but I can't seem to get that to work quite right.)


I can't sort or filter because I''m trying to automate this arbitrary lookup across functions and I'd rather not have to sort and filter the data every time it comes in.

Does that clarify things? As always, thanks for the help!

 

[sbendbuckeye]

Hey JayBird,

1. Based on your last post, does that mean you already have John, Amy and Paul sheets setup in your main file?

2. Do you have a data area within that same file that you process to store the data?

3. How does the input data get into your file?

4. Do you need to maintain links back to the data area or could you copy those values (eg could John's sheet contain 125, 673 and 210 or does it need to contain formulas pointing back to the data area)?

5. Are there no headers of any kind in the John, Amy and Paul sheets?

Good LucK!

 

[Jaybird]

Hi!

1) I am setting up the John, Paul and Amy sheets in the master file. I will be duplicating the entire file every 15 days, and pasting new information into the Input sheet, and intend to have all of the little John, Paul and Amy sheets link to the master Input sheet and auto-populate

2) The master Input sheet is actually linking data from a separate file where all of the numbers for each of the employees is calculated.

3) see above (generated and linked from another Excel spreadsheet. New data every 15 days)

4) The data could be numbers instead of links in the end, but there are hundreds of employee sheets and I would like to have this master sheet set-up to take new numbers every two weeks, so formulas are preferable

5) There are lots of headers and lines in each worksheet.


Can an offset function work? [/quote]

 

[sbendbuckeye]

Is my understanding correct:

A. You have an Input sheet in File1 which is populating ok
B. You need to split the intermixed detail information from the input sheet into individual sheets also in File1 for John, Paul, Amy, etc
C. If Sue is new in the data this week you need to create a new sheet for Sue in File1

I have a couple more questions:

A. Do you need a summary sheet with 1 line of data for John, Paul, Amy, etc
B. If so, is the summary sheet in File1 or else where

Good Luck!

 

[Jaybird]

So far you're correct (all 3 of your conclusions).

If I understand your question, the summary sheet with 1 line only (Totals) is already created in a different document.


These summary sheets are the Individual Reports I'm sending to each employee, so they know their sales and the information on each of their sales (in actuality it's not only 1 column; it's 12).

 

[sbendbuckeye]

Hey JayBird,

A. Does your input data start on Row 1, if not, which Row does it start on

B. Does your input data start on Col A, if not, which Col does it start on

C. Earlier you said 60 columns wide, so assuming Row 1 and Col A to simplify things, that would mean if Row 1 was Paul, you would want 60 formulas in Paul' sheet pointing back to row 1, is that correct

D. What Row and Col does the data start on in the individual sheets

E. Are the individual sheets refreshed each month or is data appended

It wouldn't be too difficult to write a macro to do all of the above pretty easily if there is consistency in the formatting.

Good Luck!

 

[Jaybird]

double post

 

[Jaybird]

So there's no way to use index/match/offset to link the worksheets together to do this? I am slightly embarrassed to say I've never used a macro and wouldn't be too comfortable programming it

Sounds like you're getting specific. Ok, the worksheets (there are 3) layouts is as follows:


Product 1 Master Worksheet

This is where I'm linking the data to another document that's crunching it and spitting out the final sales numbers.

A4 is the first entry:
Salesman Name

B4: Salesman Location code

C4 to P4 are all of the individual sales information (starting with Date funded and going forward.

This goes all the way down the line. If there's no entry, it just gives a "" (thanks to the guy above)

Worksheet 2 Master Worksheet - Same as worksheet 1, but for product 2



Individual Salesman Payroll Worksheet:

The Payroll Worksheet delivered to the Salesman:

Salesman Name = N2
Salesman Location Code = N3
Product 1 Date funded = A8
Product 2 Date funded = A17

This is because we sell two products. Usually they don't sell more than 6 each per pay period, so A8 through A15 is for Product 1 and A17-A24 is for Product 2.

Then columns B through O match the row in A and the columns on their respective master sheets (A8 on the individual sheet matches C4 on the first master sheet and A17 matches A4 on the 2nd master sheet. The rest follow suit)

 

[Jaybird]

And regarding the auto-updating, I actually duplicate the entire folder.

The idea is I have one blank folder (Labeled "Master") that has all of the worksheets for the month in their respective folders. I re-name is to the current month, and paste the month's data into two places, twice a month.


The reason for this is because I have several different worksheets other than just this income statement. I"m using the data from these sales reports to reconcile the General Ledger we receive from the Accounting office as well as preparing reports etc. So the "Master" folder has all of the worksheets I need already linked to each other.

 

[sbendbuckeye]

Hey JayBird,

Can you send some sample data to my signature email address without the NO_SPAM? I may be able to help you with some macro code over the weekend and then post the results back here for the benefit of others. Good Luck!