pull down to refresh
idk why is returning this error and it prompts to "FILTER".
I am not at my desk, I am using somebody's else PC. gonna try on my other PC with another excel, libre office.
reply
This look good but still it give me error.
Let me try replacing the separator , with ;
reply
let me know if it works
reply
idk why it gives me error and it prompt to that FILTER. Must be something with locales settings.
The formula looks really good, make sense and I see in your screenshot that copied the correct data as I want it.
I will give you the bounty anyways and figure it out starting from this formula.
Thanks!
reply
I wonder what version of Excel you are using. Mine was:
Microsoft® Excel® for Microsoft 365 MSO (Version 2411 Build 16.0.18227.20082) 64-bit reply
unbelivable !
On a libre office excel, same file opened and paste the formula it doesn't give error, but it display nothing. wtf is wrong with this ?
tried also on a google sheets with the same file and it gives error "circular dependency"
reply
In that specific picture, the formula is pointing to
$F2 but your ID column is on E so it should be $E2. Also, the G$1=... part should say F. Essentially, your second table is one column shifted to the left from my example.I also tried on google sheet and it should work as long as you fix that error.
reply
Yeah I saw it, still the same.
Yes, google sheets works !
reply
TIL the excel formula that contain FILTER is available only for Office 365 MSO.
The thing is mi wife have to gather some huge data (like 10k records) from 2 different excels.
If I upload those to google sheet, I cannot construct the formula from 2 different files. One file is also getting some data from another file... is a mess.
In the end I made a temporary copy of the data sheet into destination file and working from google sheets.
reply
In the end I made a temporary copy of the data sheet into destination file and working from google sheets.
That may be the fastest way to go for now.
Does your wife plan to maintain both separate excels in the future? If not, it may be good to just copy the data from the no longer needed Excel into the new one, to keep all the data in one place (assuming the two files are meant to track the same kind of data)
Yeah must be something related. I tried on another PC with office 2019.
Gonna try on mine with libre office.
reply
deleted by author
reply
=IFERROR(VLOOKUP($F2,FILTER($A$2:$C$11,G$1=$B$2:$B$11),3,FALSE),"")$F2with the column that containsIDin the wide table$A$2:$C$11with the full range of your original tableG$1with the column that contains the service code column heading in the wide table$B$2:$B$11with the original service code columnID, Servicepair in the original data appears at most once.)