Ask Your Question
0

VLOOKUP looking outside of my defined array

asked 2020-11-05 23:24:00 +0200

default_abuser gravatar image

updated 2020-11-09 16:55:33 +0200

I've only deployed VLOOKUP on one file and it's been working fine using this guide until just now. My calculation appears in every cell in Column U and is this: =VLOOKUP(H777,V:W,1,0) where column H contains a unique customer identifier (account#) in each row for hundreds of rows, and anywhere in Column V is where I'd like the calculation to look for that identifier. Column V is where I temporarily paste unique identifiers of customers who made a transaction at our website. If it does not find the value from Column H in Column V, I want it to return an error. So, after pasting the account numbers of customers who made a transaction into Column V, I sort columns A:U by Column U -- which takes waaaaaaay too long -- but if I'm patient it brings to the top all the rows of customers who made a transaction -- where I can code/remove them. Except for the exceptionally bad lag on the Sort, that has worked.

But now even though Column V (and W) are completely empty, four lines of column U where the calculations live are returning a value, and instead of an error the rest of the cells are returning a 0. Now, I can live with the 0--no one's account number is 0 so that won't be a problem. But my calculation says "look in V:W" and yet V:W is completely blank and the calculation -- on only four rows!? -- is finding a value that can be found ONLY in column H. That is to say, when I ctrl+F on the value of the cell in column V that has been returned, the only place it finds that value is in column H. I just double checked my calculations and they indeed indicate the array is V:W.

How did I break it? Does anyone have a completely better and different way of accomplishing this, as I do not love this solution. Please help.C:\fakepath\BROKEN CALCULATIONS - data removed.ods

edit retag flag offensive close merge delete

Comments

Column V is where I temporarily paste unique identifiers of customers who made a transaction at our website

Without the details of a real file hard to tell, but the temporarily makes my gut feel that you might want to check for Advanced Filters - see LibreOffice Help - Filter: Applying Advanced Filters in combination with Named Ranges and Range Options:[x] Filter

Opaque gravatar imageOpaque ( 2020-11-05 23:36:37 +0200 )edit

I've already been down that road: https://ask.libreoffice.org/en/questi...

In the end, no one stood up for filter as the tool for my task.

default_abuser gravatar imagedefault_abuser ( 2020-11-05 23:45:56 +0200 )edit

Why do you ask for alternatives, if you are in fact bound to specific solution. Bye...

Opaque gravatar imageOpaque ( 2020-11-06 16:34:42 +0200 )edit

@default_abuser, Just share a file with one row with fictitious data. I adapt the formula, and you only need to copy and paste the formula in the right place in a copy of your document to give it a try.

(Oct 22 '20, https://ask.libreoffice.org/en/questi...)

LeroyG gravatar imageLeroyG ( 2020-11-06 20:52:21 +0200 )edit

I've uploaded it. At this point, I'm just trying to figure out what went wrong so I can avoid it. In this case, I had a backup where the calculations were not (yet) broken, so all is good. But I may lose a lot of work someday if I notice the calculations are broken and now need to revert to an old backup.

default_abuser gravatar imagedefault_abuser ( 2020-11-09 16:56:33 +0200 )edit

@default_abuser, I am seen your file. What is the data that you paste in columns V and W? AcctID and …

LeroyG gravatar imageLeroyG ( 2020-11-09 17:11:29 +0200 )edit

Just account IDs and I actually paste them only into V. I have no use for column W, an array simply needs to be at least two columns for some reason.

default_abuser gravatar imagedefault_abuser ( 2020-11-09 17:14:24 +0200 )edit

@Opaque Your solution requires me to abandon entirely what I've been working on and learn entirely new things. The best solution would require no learning--I would simply just go do it with an easy click-by-click guide (that you did not provide). The current working solution -- using VLOOKUP and Sort -- was entirely new to me not long ago, but now I'm well on the way to mastery. Should I master it and judge it inadequate (and the lag may doom it to being deemed inadequate), I will move onto suggestions such as yours. But learning these new techniques takes me several days, so frankly I'm not super capable of trying more than one at a time unless they are so easy they do not require any new learning on my part and you can simply give me click-by-click instructions. Another suggestion I've received and am considering is ...(more)

default_abuser gravatar imagedefault_abuser ( 2020-11-09 17:21:36 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2020-11-09 17:58:45 +0200

LeroyG gravatar image
  • Delete old data from column V
  • Paste IDs with new transactions in column V (don't worry with order or blank cells)

  • Populate U with the formula in U2 (=IFERROR(VLOOKUP(H2;V:V;1;0);"--")): copy U2, press Ctrl+Shift+End and Shift+Left Arrow, and paste

  • Recalculate: press F9 (Ctrl+Shift+F9 if in doubts)
  • Select range from A2 to Ux (being x the last row): select A2 and press Ctrl+Shift+End and Shift+Left Arrow
  • Sort by column U
  • Remove rows if ID number is seen in column U

See sample file.

Tested with LibreOffice 6.4.7.2 (x86); OS: Windows 6.1.


Add Answer is reserved for solutions.

Press edit below your question if you want to add more information; also can comment an answer.

Check the mark (Answer markCorrect answer mark) to the left of the answer that solves your question.

edit flag offensive delete link more

Comments

Just CTRL+Shift+F9 solved the problem -- i feel entirely stupid. Some other helper on this board had instructed me to turn off auto-calculate trying to relieve me of the huge lag that ocurrs when I sort the column of calculations (it didn't help anyway) and I guess I didn't turn auto-calculate back on.

default_abuser gravatar imagedefault_abuser ( 2020-11-09 18:10:56 +0200 )edit

Great that your problem is solved! Regards.

LeroyG gravatar imageLeroyG ( 2020-11-09 18:16:38 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-11-05 23:24:00 +0200

Seen: 58 times

Last updated: Nov 09 '20