Excel VBA Search Userform
- or -
Post a project like this- Posted:
- Proposals: 2
- Remote
- #1645022
- Awarded
Description
I hope this message finds you well.
I am in need of a tool in Excel that searches for one of 5 document numbers in several Workbooks that are in the same folder. The relationship between the numbers then have to become visible.
Let me explain a bit more:
We have:
(1) E-CR's, which cause (2) A-CP's, which cause (3) A-CR's, which cause (4) Subcon-CCN's, which in turn cause (5) A-CCN's.
The names have been shortened for security reasons.
E-CR's, or sometimes called CP origin, can contain multiple A-CP's, which can have multiple A-CR's, and so on. You get the picture.
Having this 5 step document flow in mind, what I need is a tool that clearly shows all related document numbers when searching one of the 5.
To give you an example: I am typing a (4) Subcon-CCN in the tool every related document before (1 to 3) and after (5) shall be shown.
I would love to have a UserForm with several listboxes showing the results. One listbox per document number type, in the order described above. This is needed to later add Textboxes that are populated when you click on the listbox entry.
The difficulty lies in the data entries, as you probably see quite quickly from the sample files. Firstly, there is no clear 1 to 1 relationship between the numbers, as described above.
Secondly, the people working with the separate files have not taken good care of it or did not have data analyses in mind when putting in their inputs over the years.
Another difficulty will be the fact that one file may relate (1) (2) and (3) but only another document relates (2) and (5)...etc. You get the picture.
Unfortunately, I cannot upload both all 5 relevant files and my relational drawing here to give you a complete overview but 3 of the 6 files are attached here. However, before writing a small book, I would love to hear from you if you think you will be able to do what is needed and in which timeframe, incl. costs.
Best regards
Marvin M.
100% (1)New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
Hello, can you please let me know if the file format is going to remain the same? e.g. you mentioned that E-CRs may be present only in files 1, 3, 5 and then A-CPs may be present in only files 2 and 4. So can we decide on some kind of such configuration and then limit the search for any particular type of string to only certain files OR should we always go and search for every file? Another format question is whether the columns in the files will change. Again, E-CR may be present in file 1 on column 3 and in file 3 on column 5. Will this position ever change?
The best bet may be to search any string first through ALL available (5) files and then go for the relationships ... i.e. find out parent and child string values (or actually ancestor and descendant related string values) and then search for those also in all the files. Do you think that's an acceptable approach to you?
Finally, what is the output format for this?
Please let me know, Thanks.