LinkBack URL
About LinkBacks
Need help to
1. Extract every words, after "Type>", in any position within a cell.
2. Split the results into 2 columns, after cross checked with existing list
Hope these could be solved in 1 step.
Please see attachment.
Thank you
Need help to
1. Extract every words, after "Type>", in any position within a cell.
2. Split the results into 2 columns, after cross checked with existing list
Hope these could be solved in 1 step.
Please see attachment.
Thank you
Where have these come from?
Excel 2016 (Windows) 32 bit
****Top ****Bottom ****Outerwear ****Sets ****Shoes
Sheet: Sheet1
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
So until you answer the question, PQ can get us this far:
Excel 2016 (Windows) 32 bit
Types Christmas, Festive, Outerwear Chinese New Year, Sets Shoes
Sheet: Sheet1
Please Login or Register to view this content.
the list is a cross reference table.
i haven't created it yet, but it would be around 20 to 30 data within the list
crossposted: https://www.mrexcel.com/board/thread...split.1139217/
Rule 03: Cross-posting Without Telling Us
Your post does not comply with Rule 3 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.
Post a link to any other forums where you have asked the same question.
Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.
Read this to understand why we ask you to do this.
I have added the crosspost reference for you today. Please comply with this and all our rules in the future
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Hello, thank you for information & i'm really sorry for the damage i've made.
-------------------------------------------------
already wrote there, is:
i'm sorry for the damage i've made.
this is the link to this excel problem & what kind of progress has been made so far
the link...
thank you
-------------------------
by the way, i tried to paste the link above,
but when press post quick reply, it said i couldn't put any link,
so i remove the link
thank you
OK - I was working on this for you, but as you will no doubt have got a solution elsewhere by now, I'll leave it now. Please don't forget to share the solution here. Thanks.
AliGW,
thank you for your response,
i already read your first solution & reply about table you asked.
actually i don't know how to apply your solution in excel, (sorry i'm newbie).
so i'm googling how to apply it.
i will let you know if i already able to apply it
by the way, please don't get offended,
i really appreciate your help &
didn't aware at all about cross posting.
but now i knew & will be careful.
i am sorry, AliGW.
It's fine to cross-post a long as you share that with us. However, if I know you are getting help elsewhere, I tend to prioritise other queries. Hope this makes sense.
will it be ok if i'm hope the help come from you?
already try your solution:
1. i open the excel which i sent to you
2. go to Data > new query > from other sources > blank query
3. paste your code
4. this is shown
Expression.Error: We couldn't find an Excel table named 'Table1'.
Details:
Table1
which step did i miss?
thank you
Try the attached file. Just click the "Process Data" button.
You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
Practice makes perfect. I'm very far from perfect so I'm still practising.
Mumps1
Thank you for your feedback
1) i tried it & already 90% on target
current solution:
1st column | 2nd column
Outerwear | Christmas, Festive, Outerwear
Sets | Chinese New Year, Sets
Shoes | -
expected solution:
1st column | 2nd column
Outerwear | Christmas, Festive
Sets | Chinese New Year
Shoes | -
2) if this able to solve, am i allowed to edit your solution to meet real condition?
and if allowed, do you have guide/step by step to edit the code?
Thank You
The macro I suggested works on the file you posted as you requested. If your actual file is different, attach a copy of that file and explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data.
about why the solution is 90%, because
outerwear & sets should not appear in 2nd column
because they already exist in 1st column
(please see shoes, which only shown in 1st column, and not shown in 2nd column)
The PQ solution attached involves loading the first table and then merging it with the category table.
If you want full instructions then let me know.
Try:
Please Login or Register to view this content.
Hello AliGW,
yes it works,
now i will try to process the real data
Thank you
Yes, it works.
If you need more help, just shout. I will be around again tomorrow.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Mumps1
thank you for your code,
however i don't understand how to use it,
and if it's a macro/VBA, i'm so sorry because i don't know anything about macro at all.
Hello, AliGW,
yesterday i tried to adjust your excel layout, to meet the demand which is 5800 products, and i failed.
what i already did:
# moving cross reference table to another column, so i could add items to list of data until 5800 product, & also add items to cross reference table, is not working.
# try to reuse the query thru power query, but my excel 2019 said the queries in this workbook might be incompatible with your current version of excel. These queries were authored with a newer version of excel or power query and might not work in your current version.
and when i pushed it, there's an error
is not working also.
i attached the real excel (content until line 5800)
thank youAttachment 685599
Last edited by superjoejoe2000; 07-07-2020 at 02:53 AM. Reason: forget to attach image, & already able to attach excel so i attach excel, not image
The tables have to be loaded first.
If you want me to look at your new scenario, you will need to post a new sample workbook (no need for links to external sites).
Pasting data post query isn't a problem - you just need to check the query settings.
Anyway, easier to advise if you provide more realistic sample data.
Thank you for your response,
already put real excel file in my 1:42 PM post
i'd like to re-attach again here, but can not found button manage attachment,
so i will edit this post in order to make that button appear
Last edited by superjoejoe2000; 07-07-2020 at 02:58 AM. Reason: cant find button manage attachment thru normal quick reply, so i edit this post to make the button appear
You had not attached it when I replied - you did that at 01:53, so after I'd posted.
I will have a look. I'll get back to you shortly.
I think this is the best we can do given the anomalies that exist in some of your data. I added a remove duplicates step to the main query.
Hi AliGW,
actually need the results in duplicated & without sorted until line 5800
i tried to:
excel > query tools > query > edit > applied steps > 2 most bottom:
sorted rows
removed duplicates
press X (to delete)
& get this error:
expression.Error: 5 arguments were passed to a function which expects between 2 and 4.
Details:
Pattern=
Arguments=[List]
then click button > go to error > it brings me to Applied steps > 2nd line > Added index:
#"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
which part do i have to edit in order to eliminate this error?
Thank you
It's impossible to help without the workbook you have manipulated.
Here's a version with all duplicate rows left in.
there are 2 responses towards most recent excel:
1. the excel is already 99% on target,
eg: cell B 88 does not contain "type>"
so the query does not processed this cell,
query skipped line 89 also,
and query continues to process cell B 90 which contain "type>"
the problem:
the result is not appear in F 90 & G 90, but in F 88 & G 88
and every B cell without ">Type" would be skipped,
so the current result only appear until cell F 5325 & G 5325 (and the data is not inline)
2. i have wild guess about what missed in current query.
i think there is 1 or more table,
which had been erased/renamed within query steps
(maybe this for "clean/minimalist excel layout").
but this removal/rename cause the excel is not reusable,
eg:
i try to fix and add ">type" into cell which doesn't have it before,
& paste into column B 6 to B 5803,
but the result doesn't change
is it possible to:
adjust existing query,
to become keep all temporary table & avoid renaming,
so the excel could be edited & reuse?
it's really ok if the sheet would be cluttered & not minimalist,
but at least i could fix the wrong data & maybe apply simple query modification,
so i could avoid ask the same topic repetitively
Sorry - you have now shifted the goalposts, so I'm going to back out at this point.
I suggest you have a play, but without the type identifier, it's going to be very hard to get what you want.
What I have given you is a bare bones mock-up of how this could work - you have to adapt it to your real set-up. By doing this, you will learn how it works (which you are going to need to do so you can manipulate it in future).
Sorry - I have run out of steam on this one.
Last edited by AliGW; 07-07-2020 at 05:04 AM.
it's ok. AliGW
thank you so much for your help.
it really helpful
No worries. I hope you are able to adapt it.
I think you will need to hope someone has a VBA solution for the new criteria. Good luck.
What I have given you is a bare bones mock-up of how this could work - you have to adapt it to your real set-up. By doing this, you will learn how it works (which you are going to need to do so you can manipulate it in future).
Yes, i agree,
last night inch by inch i started to understand how power query works.
Thank you, AliGW.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks