Dumb Excel Questions

[SredniVashtar]

@NerdAlert

Why is it a bad idea? This way I don’t have to manipulate any data. The less I ask analysts to change data the less opportunity for error.

Imo, it’s a bad idea to tell your computer:

“please return the corresponding data from whatever column contains my lookup data for whatever column contains my key data.”

For the same reason you wouldn’t use that sentence in real life when talking to a human being. It’s hard to understand, and hard to fix when something goes wrong.

[NerdAlert]

Now I feel like my social skills are under attack as well.

[dr_t_non-fan]

Seems as if the analysts are already changing the data more than they should, especially if they know it is being used for some outside purpose by someone (you), I assume, is more important than they are.

[NerdAlert]

I wouldn’t ask a computer what their favorite number is, it would probably give me a date instead, but I do expect it to be able to answer complex logical problems!

[SredniVashtar]

To be fair, it could just be that you’re too smart for the rest of us plebs. Reminds me of reading philosophy.

Another suggestion here is to use VBA. I don’t know how far outside your wheelhouse that goes.

[NerdAlert]

Maybe I’m just so moronic I seem smart?

Arrested Development S3E6 "The Ocean Walker"

[dr_t_non-fan]

@SredniVashtar

Another suggestion here is to use VBA. I don’t know how far outside your wheelhouse that goes.

Probably the best way.
Have a program that reformats the data, various in formats, into the exact format that NA wants them,

Or,

1. Put raw data in columns A:(x);
2. Pull only the data you want into columns (x+2):(x+2+y), using INDEX/MATCH.
   Where x = width of raw data; y = width of only the data you need.
   (I like a column of space, hence the “+2”.)

[SredniVashtar]

One more ugly solution:
Create a new tab. Call it, “new data”.

Pick the columns you care about. Put them in the order you want, starting with the key.
Below the headers, do something like =index(data!$A1:$Z100000,0,Match(header,Data!$1:$1,0)).

Better still you the Match part of the formula out, so you can see it clearly above each column.
Then the new formula is like =index(data!$A:$Z,0,a1)

Also if you make a note about how it all works. And some automated checks, to make sure the data is vaguely good looking.

[SteveGrondin]

@NerdAlert

The data comes from a TPA. Their system has hundreds of output options. Each file we receive is specific to a case. One case might have attributes another doesn’t have. They don’t provide columns that aren’t applicable on a case by case basis. So one case might have 50 columns of output, another might have 60. And our key field is buried to the right of their output.

We have no control over the format or layout.

So there is one field that is used in every case file you receive, and you use it as a key. Sounds like you could ask them to put that field on the left for every report. They can say no, but I would at least ask.

So how do you use the data? Do you use the same fields from every report?

[dr_t_non-fan]

@SredniVashtar

One more ugly solution:
Create a new tab. Call it, “new data”.

Pick the columns you care about. Put them in the order you want, starting with the key.
Below the headers, do something like =index(data!$A:$Z,0,Match(header,Data!$1:$1,0)).

I think that is what she’s already doing.
I’ve tried a new tab for mine, but the number of ROWs changes each time. Putting the formulae to the right of the raw data works better for me.
My process:
0. Before first: I have existing data and formulae from prior process.

1. Paste new raw data below the prior raw data.
2. Check width; add or delete columns as necessary.
3. Paste the new headers over the old headers.
4. Insert new data into old data (plus a blank row above and below it, so you know what’s old and what’s new).
5. Paste formulae for new data.
6. Delete old data above and below the new data, plus the blank rows.
7. Check formulae columns for missing data, due to their not being there or that the heading name has been changed.
8. Done.

[The_President]

I used an index/match within a sumifs function to index the sum range to the column I wanted. I was pretty happy with this as it was a new discovery for me. I think it might work for what you are trying to do.

[NerdAlert]

Go on…

[The_President]

=Sumifs(Index(B:D, 0, Match(“XXXX”,B1:D1,0)), criteria range_1, criteria_1,…) where XXXX is the column heading in your data.

[SredniVashtar]

She wants the criteria range to also be index matched. Which will work, but is ugly.

[The_President]

I think you just repeat the same logic for those ranges?

[Maphistos_Sidekick]

@SteveGrondin

So there is one field that is used in every case file you receive, and you use it as a key. Sounds like you could ask them to put that field on the left for every report. They can say no, but I would at least ask.

When dealing with TPAs or brokers/agents, who use one of a number of competing systems (or custom-built systems) to provide the same reports to umpteen different carriers (or whomever) that all want similar data, but have varying preferred formats… absent a large, profitable, possibly exclusive relationship, the answer will most likely be “no”.

In a past life, I was asked a similar question – “gee, we get loss runs from our brokers; can we ask them to put it in our special format so we can easily put it in our rating tool and archive it for future modeling?” – from a sequence of bosses and interns. I and my underwriters took bets on how quickly the question would be asked when someone new came around, and how they would react when we just laughed in response…

[Chalky]

for sources with data that has field changes from time to time, I like to keep a separate tab listing (vertically) the fields in the current order, then I paste-transpose next to it the fields of the new data. Then right away you can see any differences in the fields and go on accordingly

[Vorian_Atreides]

You could also take the new data and copy/paste-transpose the new headers next to the “prior” order as well.

And instead of using an embedded INDEX/MATCH, you could set up a VLOOKUP with this set up where you do a MATCH on the desired heading against its location within the new dataset.

I think.

[SteveGrondin]

Yeah, our hedge area fights this with all the different counterparties. Each counterparty has their own format. But at least those formats stay pretty consistent over time for the same counterparty.

I guess I am trying to imagine how the TPA (assuming it is one TPA and not multiples) creates the reports. I would think Case type A, Case type B, etc. would each have a standard format so the data jockey doesn’t have to manually pull in whatever columns they feel like today, but perhaps it is too fluid.

This screams for a DB approach.

[Chalky]

do you mean something like:
INDEX (range, MATCH(match your key), vlookup (lookup field number))