Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have to do a simple vlookup between two tables in Excel.
Simple enough.
The remaining hurdle is: The one Excel_file (from where I have to fetch a value if there is any) has 2-line records - and
I need
- the item_nr. from the 1st line of every record
- the BIN nr. from the 2nd line of every record
=> So I cannot use a WHERE ISNUM to filter for just the lines with item_numbers for then all the BIN_numbers would not be connected.
Can anyone help me on this?
Thanks a lot!
Best regards,
DataNibbler
P.S.: I cannot use the logic that all odd-numbered lines have an item_nr. and the even-numbered have a BIN_number and then JOIN them because sometimes there are several lines for one item_nr., so that can vary.
Hi,
this is solved now.
For whatever reason, the VLOOKUP wouldn't work in Excel with a formula, only with a directly typed-in value - if, however, you multiply your formula with 1, that is registered as a numeric value 😉
Then it works.
I'll close this thread now.
Thank you very much for all your help!
Best regards,
DataNibbler
Hi DataNibbler,
I would probably try to prepare the xls-load and split every record into two records and use the resulting table in a where-clause, maybe in this kind:
table:
Load
recno() as RecNo, rowno() as RowNo,
subfield(Field, chr(10) & chr(13), 1) as SplitField1,
subfield(Field, chr(10) & chr(13), 2) as SplitField2
From xyz;
Maybe you used no third parameter for subfield if you have an unknown number of nested records.
- Marcus
Is there any primary key field that exists on both rows of the same record?
For better understanding can u please share the sample data..?
Hi pradeep,
thanks a lot for your help! No, there is no key I could use to join the data to the item_number.
There is nothing. Really chaotic.
I guess I'll have to ask from where that list was exported and whether it can be exported in any other way. Maybe something was left out though I doubt it.
PFA a sample_file that looks like it - just the two columns that I need.
Hi DataNibbler,
a split or other preparing isn't possible? You will need something or you could forget it ...
- Marcus
Hi Marcus,
yes, I'm afraid you're right. I will ask if anything can be done about that format.
But maybe - all I'd have to do is actually populate the item_numbers downward - I could use PREVIOUS to just copy one number from one line to the next until I encounter another one? Could that work?
Hi DataNibbler,
if you could use subfield with a third parameter you will be have all information on one record. If not you could use previous or peek to bring the data in a correct order - maybe you could for this also use a combination from recno and rowno.
Maybe you also need some additional cleaning with keepchar(), replace and so on.
- Marcus
Hi Marcus,
what do you mean with Subfield? I can't figure out any way to do with that function?
I'm just experimenting with that PEEK() or PREVIOUS(), but that doesn't quite work yet.
Hi DataNibbler,
with subfield you could split a string into pieces with a defined delimiter. In a load-statement you could use a fixed version with a third parameter which will split only in these string-parts which a called and by the version without a third parameter it will create for each part a new record - which you could easily track with recno and rowno.
- Marcus
Hi Marcus,
yes, I know that use, you explained that to me a while ago and I used it successfully.
But I still cannot imagine how I could use it in this scenario?