The Swamp

Yo Excel Wizards and Jerloma,

Is there a formula that will pull row and column values from an entire array? MATCH appears to only search one column or one row.

I have a table that has values from 1 thru whatever (in increments of one) and need to pull the values from the row and column (ie value 1 is at x=2500, y=3000 or something like that, I need to search for "1" in the table and get 2500 and 3000). If I can get the row and column numbers I can use another formula to do this, but right now I'm stuck. Anyone got a solution?

Wait I could maybe use Index for this and just make it messy as hell, no?

EDIT: NM, Index just gives the value at the row and column. I need that in reverse.

Internet's best solution is to multiply it by arrays containing the values of the rows and columns using SUMPRODUCT...is that my best bet?

Hmmm. Probably needs a Couple of VLOOKUP columns for each value? I’m not 100 percent sure I know what you are asking

SUMPRODUCT worked....I am searching a matrix for option plays I want to look at that meet my criteria and each match gets a value (ie the first match gets a 1, second gets a 2, etc. etc.). So to lay it out for me to look at in the terms I need to look at it in, I need to get "2630" (row) and "2580" (column), which signifies that I want to look at buying a 2580 option and selling a 2630 option for Amazon in this case. With these values, then I do the VLOOKUP.

I made two corresponding arrays that have the row numbers and column numbers, ie:

1 2 3 4 5 6 7
11 2 3 4 5 6 7
21 2 3 4 5 6 7
31 2 3 4 5 6 7
41 2 3 4 5 6 7
51 2 3 4 5 6 7
61 2 3 4 5 6 7
71 2 3 4 5 6 7

and

1 2 3 4 5 6 7
1 1 1 1 1 1 1 1
2 2 2 2 2 2 2 2
3 3 3 3 33 3 3 3
4 4 4 4 44 4 4
etc etc.

and multiplied the arrays with SUMPRODUCT. So the first cell matches "1" in my results array and multiplies an array full of zeros with a single one against the row array or the column array to get the value. It worked so it's kinda messy but fine for this usage.

Not a question, but I get to use Vlookup for the first time in a while. Kinda excited.

Update: It was fun.

A_B wrote: ↑Thu May 21, 2020 12:17 pm Update: It was fun.

Just wait to you try Hlookup or the joy of using indexmatch instead of vlookup (Lookup to the left and ability to separate lookup and return columns)

wlu_lax6 wrote: ↑Thu May 21, 2020 2:42 pm

A_B wrote: ↑Thu May 21, 2020 12:17 pm Update: It was fun.

Just wait to you try Hlookup or the joy of using indexmatch instead of vlookup (Lookup to the left and ability to separate lookup and return columns)

Still waiting to gain access to XLOOKUP...

wlu_lax6 wrote: ↑Thu May 21, 2020 2:42 pm

A_B wrote: ↑Thu May 21, 2020 12:17 pm Update: It was fun.

Just wait to you try Hlookup or the joy of using indexmatch instead of vlookup (Lookup to the left and ability to separate lookup and return columns)

I use indexmatch strings all the time and love it. Best thing I like about it is you can easily include other functions inside the match function. I’ve built a number of complex daily reports that need to pareto an ever changing list of items and display them mtd. Using the rank function and then an index match I can pull out the top ten each day automatically and even give it functionality where you can select any day you want to see what it looked like that day. Almost impossible to do with just vlookups.

I'm going to have to look into this - there are so many examples where I just spend a full sheet rearranging items so VLOOKUP can find them.

Gunpowder wrote: ↑Fri May 22, 2020 6:18 am I'm going to have to look into this - there are so many examples where I just spend a full sheet rearranging items so VLOOKUP can find them.

Yeah, my data was already formatted in a similar format so vlookup was no issue, but Indexmatch does look to have a lot of utility.

Here is my Excel question. The org I work for wants to do a mass mailing to our 23k person database. That's easy. What isn't easy is they don't want 5 mailers going to the same address. We have some accounts in there with husband, wife, kids, etc. because our system was used to register people for our 5k runs for many years.

I don't want to delete duplicates because I want at least one mailer to go to each address. Is there a way for the system to find the duplicates and show them to me so I can delete the ones I want to delete?

Does that make any sense?

As long as you have consistent formatting, yes. If its column A street, column B city ... you just select the entire list across all columns and rows and then go to the data tab -> remove duplicates. I'd recommend doing this in a copy file just in case you mess it up the first time.

(This won't catch differences like "New Jersey" and "NJ" but if everyone has the exact same address, it would just remove the extra row(s) keeping just one.)

I suck at Excel and people will probably laugh at this suggestion, but what about a pivot table?

mister d wrote: ↑Thu Aug 13, 2020 11:46 am As long as you have consistent formatting, yes. If its column A street, column B city ... you just select the entire list across all columns and rows and then go to the data tab -> remove duplicates. I'd recommend doing this in a copy file just in case you mess it up the first time.

(This won't catch differences like "New Jersey" and "NJ" but if everyone has the exact same address, it would just remove the extra row(s) keeping just one.)

That would work, but all address info is in the same cell, address, street, city, state, zip. I'm going to see if I can fix it to put all of that in separate columns. Thanks.

Giff wrote: ↑Thu Aug 13, 2020 11:47 am I suck at Excel and people will probably laugh at this suggestion, but what about a pivot table?

I suck at Excel also and I'm not even sure I know what a pivot table is!

bfj wrote: ↑Thu Aug 13, 2020 11:57 am

mister d wrote: ↑Thu Aug 13, 2020 11:46 am As long as you have consistent formatting, yes. If its column A street, column B city ... you just select the entire list across all columns and rows and then go to the data tab -> remove duplicates. I'd recommend doing this in a copy file just in case you mess it up the first time.

(This won't catch differences like "New Jersey" and "NJ" but if everyone has the exact same address, it would just remove the extra row(s) keeping just one.)

That would work, but all address info is in the same cell, address, street, city, state, zip. I'm going to see if I can fix it to put all of that in separate columns. Thanks.

The same cell? Might as well have written it in Notepad.

but the remove duplicates would still work in that instance, right?

Single cell would work with what I said too, with the same "if any character is different it will read them as different" caveat.

bfj wrote: ↑Thu Aug 13, 2020 11:57 am

Giff wrote: ↑Thu Aug 13, 2020 11:47 am I suck at Excel and people will probably laugh at this suggestion, but what about a pivot table?

I suck at Excel also and I'm not even sure I know what a pivot table is!

It's the Excel version of Clippy, but it's David Schwimmer.

duff wrote: ↑Thu Aug 13, 2020 11:58 am

bfj wrote: ↑Thu Aug 13, 2020 11:57 am

mister d wrote: ↑Thu Aug 13, 2020 11:46 am As long as you have consistent formatting, yes. If its column A street, column B city ... you just select the entire list across all columns and rows and then go to the data tab -> remove duplicates. I'd recommend doing this in a copy file just in case you mess it up the first time.

(This won't catch differences like "New Jersey" and "NJ" but if everyone has the exact same address, it would just remove the extra row(s) keeping just one.)

That would work, but all address info is in the same cell, address, street, city, state, zip. I'm going to see if I can fix it to put all of that in separate columns. Thanks.

The same cell? Might as well have written it in Notepad.

I realized that the way I wrote the query from our client software was giving me the address in one cell. I was able to split it out into different columns and now I can use conditional formatting to find the same addresses. I don't want to use the client names bc we may have 20 people who all used their work address as a part of a race team. But if I just use street address I can see all the duplicates and then go through them and find what I need.

Thanks for saving me a shit load of time for a project I may or may not have to do.

TWILTS

teeteebee wrote: ↑Thu May 21, 2020 6:00 pm

wlu_lax6 wrote: ↑Thu May 21, 2020 2:42 pm

A_B wrote: ↑Thu May 21, 2020 12:17 pm Update: It was fun.

Just wait to you try Hlookup or the joy of using indexmatch instead of vlookup (Lookup to the left and ability to separate lookup and return columns)

Still waiting to gain access to XLOOKUP...

Not as fun as XL Hookup.

=if(D>9”,”Yes”,”No”)

Microsoft 1- Human Genome 0
https://www-theverge-com.cdn.ampproject ... ding-dates

Have a date with a random year, example 6/6/1995. I want a formula that returns just month and day so I can then calculate a date 119 days later.

So given 6/6/1995 the answer I want is 10/4/2020
and given 6/5/1973 the answer I ultimately want is 10/3/2020

=DATE(YEAR(A1)+2020-YEAR(A1),MONTH(A1),DAY(A1)+119)

(I'm not getting the same answers as you for +119. If you want yours, I guess its the same formula +120.)

((Knowing this means anything before March 1st would potentially return different dates depending on whether its a leap year.))

mister d wrote: ↑Mon Sep 14, 2020 2:14 pm =DATE(YEAR(A1)+2020-YEAR(A1),MONTH(A1),DAY(A1)+119)

(I'm not getting the same answers as you for +119. If you want yours, I guess its the same formula +120.)

((Knowing this means anything before March 1st would potentially return different dates depending on whether its a leap year.))

Ah the add the year remove the year move...Did not think about that manipulation.

Can I ask what this could possibly be for?

mister d wrote: ↑Mon Sep 14, 2020 4:25 pm Can I ask what this could possibly be for?

My company has required continuing education for the firms that are in our industry. We have a fancy new reporting tool we exposed to the staff at these organizations that let them build their own reports and such. However, as soon as someone complete the CE the values for the window in which they have to complete their CE goes to "null". However, we do make available their base date that the CE window start date is calculated from (have to take CE 2 years after you start and then every 3 years after that and you get a 120 day window to complete it). One firm has an analysis they do to figure out how many days before the deadline people complete their CE requirement. Was thinking about a way this firm could do it their self after exporting almost the perfect report.

This would not be necessary if the person's full CE history was passed to the fancy reporting tool.

A group of family lawyers is trying to come up with a spreadsheet to use in standard divorce cases. It's really a basic spreadsheet, some addition, some subtraction, a minor bit of division (getting parties to 50% each of assets and liabilities), and they presented it last night as a .pdf. The problem with it as a .pdf (beyond only being able to handwrite on it), is that they only had 2 or 3 rows for a specific type of asset (i.e. 3 entries for bank account). Is there a way to make an Excel form(template?) where you can add rows, and the cells for summary or total remain unchanged?

Maybe the other alternative way to say this would be to say is there a way to have a spreadsheet that would have 20 rows, but if you only enter information on 3 of them, the rest of the blank rows would automatically go away in a certain view?

govmentchedda wrote: ↑Thu Feb 04, 2021 4:55 pm Is there a way to make an Excel form(template?) where you can add rows, and the cells for summary or total remain unchanged?

Should automatically add new cells to the function if they're added between other cells of the same function. (At least gsheets does this)

govmentchedda wrote: ↑Thu Feb 04, 2021 4:55 pm A group of family lawyers is trying to come up with a spreadsheet to use in standard divorce cases. It's really a basic spreadsheet, some addition, some subtraction, a minor bit of division (getting parties to 50% each of assets and liabilities), and they presented it last night as a .pdf. The problem with it as a .pdf (beyond only being able to handwrite on it), is that they only had 2 or 3 rows for a specific type of asset (i.e. 3 entries for bank account). Is there a way to make an Excel form(template?) where you can add rows, and the cells for summary or total remain unchanged?

Maybe the other alternative way to say this would be to say is there a way to have a spreadsheet that would have 20 rows, but if you only enter information on 3 of them, the rest of the blank rows would automatically go away in a certain view?

Will you please have the lawyers step away from excel. Back away slowly. And hire Mister D to fix this for you.

If I were setting up this form, I'd probably just create a lot of bank account rows then a macro behind it to hide the blanks before you PDF.



(Or, if you don't need the detail on the final product, just a separate input tab that feeds a single "bank accounts" row on the main form.)

mister d wrote: ↑Thu Feb 04, 2021 5:30 pm If I were setting up this form, I'd probably just create a lot of bank account rows then a macro behind it to hide the blanks before you PDF.



(Or, if you don't need the detail on the final product, just a separate input tab that feeds a single "bank accounts" row on the main form.)

Nvm. This way is better. Just send that exact quote from Mr d to whoever is creating. And send me the response.

mister d wrote: ↑Thu Feb 04, 2021 5:30 pm If I were setting up this form, I'd probably just create a lot of bank account rows then a macro behind it to hide the blanks before you PDF.

That sounds really useful - how generally does one do that? (Is that the kind of thing that can be briefly explained without having to go into huge detail?)

There are fancier ways but the quickest is have A and B outside the print range, where A is the row number and B is A + 100 if the bank account dollar cell is blank, then the macro can sort it below the print range.