+ Reply to Thread
Results 1 to 116 of 116

Searching and comparing data from multiple sources with non-contiguous "headers"

  1. #1
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Searching and comparing data from multiple sources with non-contiguous "headers"

    Firstly, I will apologise for the lack of any "working" example. I wasn't sure how to present it.

    Also, I'm hoping that this can be completed using worksheet formulas, CSE formulas if necessary - but avoiding VBA if possible, because Ihave no clue about VBA - however, if VBA is the only viable option, it will have to do.

    To start - my source data is exported from 3rd party software, I need to do as little with the source as possible to make this non-technical-end-user friendly.

    Problem #1 - locate a value based on its position in a matrix and then return this value to a master matrix.

    In my poor example, there are 3 source matrix's (ABC, ABD & ACD) and 1 master matrix (ABCD)

    Assuming I wanted to find the value that intersects A & B I want to be able to do an INDEX MATCH MATCH on the source data.

    The source uses the same values for both the X & Y coordinates in the matrix. In my export data, the staring cell for the "headers" is always B2. The headers for each row/column appear at the top of the column and end of the row, forming a diagonal. The total number of headers varies from sheet to sheet, but always follow the pattern B2,C3,D4,...,X24,Y25,Z26

    The problem is MATCH will only work with a contiguous range. I *can* put in a "helper" row/column, but want to avoid this if possible as I'll have to add it to all the source files manually, as will anyone who comes after me. So if there's anyway to convert the non-contiguous array of cells to a virtual range, or trick Excel into thinking that, withing a worksheet formula I'm all ears.

    Otherwise I have this working with "helper" rows/columns.



    The second part of the problem is checking multiple sheets and returning every match. Ideally I will be dropping the sheets as complete workbooks into a folder, and I'm hoping to be able to search all files within that folder. I know I can do this by explicitly referencing the file and sheet names, but I would like the formula to be dynamic and just search everything in the target folder. Worst case scenario, the sheets can be imported into the master workbook, in which case it just needs to search all the sheets barring the master sheet.

    Every result should be returned into the same cell, with a refernce to which sheet it came from. So in theory the INDEX MATCH MATCH should return the contents of a specific cell within the sheet that identifies the sheet AND data at the intersection in the format ID, VALUE;

    Obviously I am going to have to CONCATINATE the results to get them in the same cell.

    To prevent returning results from sheets that have only 1 or 0 matches to the MATCH criteia, I presume I am going to need some form of conditional statement like IF, SUMIF, or COUNTIF - not that I'm overly sure how to implement the latter.




    Lastly - as if I'm not asking too much from Excel and you guys already - I would like duplicate results to only be listed once, but with all the sheet IDs against them

    Going back to my "Poor Example" - we have the following values

    ABC
    A,B = 10
    A,C = 20
    B,C = 10

    ABD
    A,B = 10
    A,D = 25
    B,D = 15

    ACD
    A,C = 15
    A,D = 30
    C,D = 15

    This should return the following in ABCD

    A,B = ABC, ABD 10;
    A,C = ABC 20; ACD 15;
    A,D = ABD 25; ACD 30;
    B,C = ABC 10;
    B,D = ABD 15;
    C,D = ACD 15;
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    OK - firstly let me once again apologise for the poor example above.

    I have created a better example, see attached. I am still using fake data as it's all too confidential.

    In this 1 workbook I have 5 sheets - the master sheet, 2 data sheets, and 2 helper sheets (one for each data sheet)

    With the help of a helper row and column on the data sheets, I have used INDEX MATCH MATCH to populate a duplicate of the master sheet, using COUNTIF to knock out and #N/A values.

    On the master sheet I have simply concatenated the 2 helper sheets together using a newline character to better present the data.

    What I want to be able to do is to get the Master sheet result without using helper rows, columns, or sheets. Particularly the sheets, because as new stuff gets added, ALL the helper sheets would need updating to match.
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    I appreciate that this is not a paid help forum, as such that there is no requirement to respond.

    I'm also aware that a lack of responses may also be due to ambiguity in my op...

    If there are any points that require clarification, please ask.

    Also, please use the reference material in my 2nd post.

    Many thanks in advance to anyone that can help.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Can I just clarify (because there's a bit too much here for me to trawl through) - is it the master sheet that you are trying to create? If so, why in this format with multiple values in individual cells? Is there a reason why it must be in this format?
    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.

  5. #5
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102
    Yes, it's the master I'm trying to create.

    The source data has the headers on the diagonal. It's a reference lookup, much like a mileage chart - hence the example in the second post. It's not a mileage chart, but the function is identical. The user uses each chart friending on the job at hand.

    Several charts have the same headers, but some have different values. Each individual chart, despite some crossover with other charts is unique.

    The master sheet is to help me locate where the same lookup gives differing values.

    Once we've identified, and removed all the differing values the master will then be used to create the data sheets (that bit is easy) to reimport back into the third party system they come from.

    If it were just a couple of sheets, I wouldn't bother, but there's 30+ sheets.

    Ideally I only want to print a value once and have all matching IDs on the same line against it as it will save vertical height. Same goes for not putting new lines where there is no data.

    But I figured that might be too complex, so if work with just getting the data together first
    Last edited by AliGW; 11-03-2019 at 09:30 AM. Reason: Please don't quote unnecessarily!

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Are you open to a different approach?

    In what format does the data need to be in for reimport? We may be able to kill two birds with one stone.

  7. #7
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102
    I'm open to any approach.

    The data needs to be in the format of the data tabs MINUS the helpers.

    The master, if used, needs to contain all the headers from all the data tabs
    Last edited by AliGW; 11-03-2019 at 09:40 AM. Reason: Please don't quote unnecessarily!

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    How do you determine which are the correct values and which incorrect?

    Administrative Note:

    Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

    For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

  9. #9
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Apologies for the quote, I'm on mobile, I hit the reply button to the message rather than using the box at the bottom.

    (Note - on mobile there is no reply with quote, just reply and report post)

    As for which data is correct. That needs to be assessed by the commercial department.

    I'm creating a report that shows where the same reference lookup had different results.

    They then need to decide which is the correct one, as it could have a knock on effect to other results.

    That's why I'm making the master comparison table to start.

    I suppose you could say that ALL the data values are correct at the moment.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    I know about working on a mobile device - I do it frequently myself. When you do, you can select the quoted section and delete it.

    OK - I understand the process now - thanks. Going to see what I can come up with.

    Just one further question: in EXACTLY what format do you get these data tables? ALL of the tabs in your workbook have some form of manipulation by you. Can you please provide one tab that shows data in completely untouched format?

  11. #11
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Yup, give me a minute

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Thanks.

  13. #13
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Right, have had to enable desktop view because I can't see how to attach in mobile view

    Attached is how the data would be presented to me for Data A

    In the examples above, I would want Hull, Leeds to only show "A 60", and not "B --" as that is irrelevant. Similarly Hull, York should show "A, B 38" instead of "A 38" and "B 38" on separate lines.

    Assuming there was an item that was different (not shown in my example above), I would want the result to be something like "A, C 10" on 1 line and "E 15" on a second line with B and D ignored due to no value. And for a new line for each new value.

    If that makes sense.
    Attached Files Attached Files

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    That's great. Going to take a look and get back to you.

  15. #15
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Thank you.

    Just one last side thought...

    The data tables are of varying sizes. However, none will be above 50 headers, so if you need to use helper columns, maybe starting them in column 52 (BA) would be a good idea.

    I kept them close to the day in my above sheets to make it easy on the eye.

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Thanks for this. I'll let you know if I find an easy way to do this. I am looking at PowerQuery - others may have ideas about doing this with formulae.

  17. #17
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Ok. Never heard of PQ until the other day, so have no idea about that, but it sounds like it might do the trick.

  18. #18
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    I'm struggling with it at the moment, so if anyone has a formula solution, it might be quicker!

    I'll keep trying.

  19. #19
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    That makes 2 of us then

    Thanks for trying though.

  20. #20
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    LOL!

    I've put out a cry for help - let's see what happens.

  21. #21
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Hi

    I think a formula-only approach could be very tricky with so many worksheets to process.
    I think the best approach would be via vba, although I understand your hesitance.

    I have attached an example file to demonstrate this.
    I have included 3 additional data sheets, with your original 2 data sheets.

    To simplify the processing involved, I have organised it as a Step 1 to Step 7 process to help you follow what's going on.
    After clicking a Step x button (in sequence of course), you can view the data sheets to see what has changed, and then come back to the [Master] sheet to continue with the Steps.

    The process could be speeded up, and put into a single routine - I've just tried to make it easier to follow.

    zeddy
    Excel Blindside Flanker
    Attached Files Attached Files

  22. #22
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Thanks - I'll take a look when I get home.

  23. #23
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Well, wow!

    The re-ordering of the cities made me re-think what I'm doing - and that's a good thing because I was over complicating things a little.

    So in the end, due to the way my fies would split, I was going to create 2 or more master sheets - but that would also mean maintaining both. It does mean that the end result would be more readable to the human eye, but considering the size of the sheets that's not entirely important, plus - if I really want to, I can work back from this.

    What you have done is great, I've had a quick look at the code and most of it goes above my head.

    I've stripped out the extra data sheets and changed some values - just to simplify

    Is there any way to get the items with duplicate values onto the same line?

    zeddy.png

    So that C5, instead of showing

    A: 47
    B: 47

    shows

    A,B: 47

    Yet where the values are different they stay as you have placed them, i.e. A5

    A: 70
    B: 66

    Lastly - if I am reading the code correctly - what little I can undertand - am I correct in saying that the name of the sheet is irrelevent and that it is usung the position of the sheet in the workbook?

  24. #24
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    I've just realised something that may be important...

    Due to the number of data sheets I will have, and the way the data crosses itself, it may be better for the MASTER table to have a header row and column instead of the diagonal headers. It might reduce the size of the finished table, and I don't want to lwave any data out!

    I won't be importing the master into the software, just the adjusted data sheets once we've identified values that need changing...

    EDIT

    Scratch that, should be fine as is. Overthinking again.

    Going to try it with real data a bit later. Off out for dinner now.
    Last edited by escribblings; 11-03-2019 at 03:35 PM.

  25. #25
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Managed to work out a way of using my data without revealing the confidential nature of it (although it took bloomin' ages to redact the 3 files I've used so far).

    So it's working with the VBA shown above, and I've attached with live data.

    zeddy2.png

    It's already thrown up a couple of inconsistencies - which is what I want it to do. It would just be ideal if I could get matching values on the same line OR at least find a way to highlight cells that dont match.

    In fact, highlighting would probably be the best option as all it requires is a check on whether all the cell values are equal, and highlght the cell if they're not - but I have absolutely NO idea on how to code that.

    The only other request I have regarding formatting is if the numbers could be right aligned and set to 2 decimal places? I've tried formatting the results and nothing happens re the number format. The alignment doesn't work properly, the last or only row in a cell butts up to the right of the cell, but the rest are indented slightly. (I would like to keep the headers left aligned though.

    Thank you all for your help, this is very much appreciated.

  26. #26
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Hi

    am I correct in saying that the name of the sheet is irrelevent and that it is usung the position of the sheet in the workbook?
    You are indeed correct.
    I see you are already learning to read vba code.

    If the [Master] sheet is always the first sheet in the workbook, then you can use this in vba code:
    Please Login or Register  to view this content.
    ..instead of
    Please Login or Register  to view this content.
    Using the sheet index in the code means you can have your actual sheet tab named in English, French, Polish, Russian etc etc etc without changing the vba code.

    We can also use vba code to process worksheets according to their tab colour rather than actual sheet names (e.g. process all the [red] tabs or [green] tabs and ignore any tabs with other colours etc etc etc.

    zeddy
    Excel Toast Quality Assurance Manager

  27. #27
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Hi

    Regarding your post#25, I'll have a look at this, but the short answer is yes, this can be done.
    I'll try and post an updated version as soon as I get back from an urgent mission.
    Thanks for posting your updated example.
    This helps a lot.

    zeddy
    Excel Hadron Collision Investigator

  28. #28
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    And thank you for taking the time to help me with this.

    It's very much appreciated.

    I have a very BASIC (pun intended) knowledge of programming, so some things make sense, but it's knowing the constructs and syntax of the language.

    Thank you again.

  29. #29
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102
    Just in case you hadn't noticed - I'm sure you have.

    The "headers" have been prefixed with a ' to force them to be read as text.

    I also found out the hard way that changing the letters A B etc to numbers provided some very strange results.

    The highlights in the image above were fine manually to show what I'm looking for.
    Last edited by escribblings; 11-04-2019 at 08:38 AM.

  30. #30
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Hi

    see updated attached file.

    This will highlight cells as you suggested.

    Will post a longer explanation tomorrow!

    zeddy
    Excel Quantum AI Decoy Unit
    Attached Files Attached Files

  31. #31
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Thank you.

    Can't test it until I get home as mobile Excel doesn't support VBA.

    But much appreciated. Hope to comment on a few hours.

  32. #32
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Just had a quick look and that's briliant - picked up so many I would have missed just looking at it with the n@ked eye!

    Now just need to sort out the cell alignment, because Right Aligning the results does this:

    zeddy right.png

    I'll need them right aligned because the identifiers will be of different lengths, but the values need to line up.

    And 1 last request...

    I note you have the headers running across the top... could I possily have them running down the left side as well?

    You've seen how big this is with 3 tabs, I have another 27! to add.

    That way, I'll be able to split and freeze so I'll always be able to easily identify the intersection.

    You have been really helpful, i very much appreciate it

  33. #33
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Right - well I've sorted some of that myself - yay me!

    In Step 1 I changed
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    Which has produced the same results as trying to right align after the VB code has run. Namely if there is more than 1 item in a cell, only the bottom item is fully right aligned.

    In step 2 I changed
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    This shunted the main page over by 1 columnm. (Why is it referring to the column as a row?)

    In step 7 I then commented out
    Please Login or Register  to view this content.
    Which keeps the left hand list.

    So this leaves me with trying to remove the strange padding in all the general cells, and left aligning the left list and the diagonal.

  34. #34
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Right, sorted the alignment issues in the main cells...

    It was this:
    Please Login or Register  to view this content.
    So I found this: https://www.wallstreetmojo.com/vba-new-line/

    vbCrLf and vbNewLine yield the above results

    vbCr puts it all on one line

    vbLf and Chr(10) however yield the result I want :D - I've gone with vbLf

    Which just leaves me working out how to left align row A and the diagonal cells.
    Last edited by escribblings; 11-04-2019 at 08:05 PM.

  35. #35
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    By George I think I've done it!

    So in Step 1 I have added
    Please Login or Register  to view this content.
    And in step 5, under r, I have added
    Please Login or Register  to view this content.
    and in the for loop I have added
    Please Login or Register  to view this content.
    I would appreciate it if you could check to make sure I haven't broken anything.

  36. #36
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    More ammendments

    I've commented out step 3 (I haven't edited the buttons yet) as I want the data sorted as it appears in the source files.

    In step 5 I've commented out
    Please Login or Register  to view this content.
    so A1 is not yellow

    and added
    Please Login or Register  to view this content.
    to the loop to merge 2 cells to the right to fit the full size headers while maintaining narrow columns for the data.

    and in step 7 I've changed
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    to force the cells as text. Some of my IDs are integers, and this is causing wierd results which this fixes.

    Lastly I've added step 10 to freeze the first row and column
    Please Login or Register  to view this content.

  37. #37
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Hi

    For goodness sake - you are becoming a vba programmer.
    Top marks on your progress.

    I can't find ay issues with what you've done.
    You should be very pleased.

    As a reward, I have added a feature you might find useful when analysing your data:
    If you double-click any cell on the [Master] sheet, this will trigger a 'double-click-detection' routine for that worksheet.
    This routine will check if the 'double-clicked' cell has a red interior.
    If it does have a red cell interior, it will execute a new routine..
    Sub scrollToCellDataSource()
    ..which will loop through all data sheets, placing the 'source' ref of that double-clicked cell in the top-left-corner of the data sheet i.e. scrolls the window to put the first source in the top-left-corner.
    It will then find the second source ref, and put the cellpointer in the relevant cell.

    Test it by double-clicking any red cell in the attached workbook.
    Please le me know if this is useful for you.

    zeddy
    Excel Big Door Superintendant
    Attached Files Attached Files

  38. #38
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    It certainly sounds useful.

    I won't be able to test it until tomorrow though.

    Don't finish work for another 7 hours, then have a union meeting to go to.

  39. #39
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102
    Quote Originally Posted by zzzeddy View Post
    ...which will loop through all data sheets, placing the 'source' ref of that double-clicked cell in the top-left-corner of the data sheet i.e. scrolls the window to put the first source in the top-left-corner.
    It will then find the second source ref, and put the cellpointer in the relevant cell.

    Test it by double-clicking any red cell in the attached workbook.
    Gave it a quick spin - the workbook flashes add the green box ends up around the clicked cell. But I don't see anything in the to left!

  40. #40
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    I've thought about this.

    Thank you for the idea, but I don't think the double click is going to be that helpful.

    However, 2 things that would be are:

    A second tab called report, once the master tab is complete, a rule checks the master for any cells filled red then, returns them to the report sheet in a table with the associated row and column headers in the next 2 cells.

    And a rule run on all the data tabs that checks each cell value against those above and to the right - of any cell above or to the right is of a higher value, fill red. (With the exception of cells that are blank)

  41. #41
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Hi

    Just back from an urgent trip..
    In case you misunderstood, double-clicking a red cell on the [Master] sheet is for diagnostic purposes to check the data sheets for that red cell.
    After double-clicking a red cell, you then check each data-sheet in turn:
    For example, if you double-click a red cell that has, say, (151) in the corresponding header row 1 and, say, (439) in the left-hand column A, then when you look at the data sheets, you will find (151) in the top-left and the cellpointer in the row for (439) etc etc etc

    The idea is to trace the data sheets for those cells that are shown as red, to confirm that the process for identifying which cells should be shown as 'red' is indeed correct. Especially if you have entries from 27 or so data sheets.

    The idea for a second tab called [report] sounds good.
    I will look at this tomorrow.

    zeddy
    Excel Holographic Protection Division

  42. #42
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Ah, gotcha...

    I'll have another look tonight.

    I'm going to have a crack at the code for the report myself....

    Let's see how far I get (wife depending).

  43. #43
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Hi

    Rule 1: Your wife is the most important person on the planet.

    There are no further rules.

    zeddy
    Excel Salvation Minister

  44. #44
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Haven't had chance to do that yet - and falling asleep now - but I have edited step 7

    I've added
    Please Login or Register  to view this content.
    which prevents empty cells being returned to the master, and subsequently flagged red, like the intersection of (169) and (493).

    Please Login or Register  to view this content.

  45. #45
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    I've added in the Report tab and reworked the If statements to point to sheet 3 as the 1st data sheet...

    I'll try and look at the rest of the code tomorrow.

    What I'm thinking is along the lines of:

    Please Login or Register  to view this content.
    Attached Files Attached Files

  46. #46
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Right - I'm at work so can't test anything as I don't have access to macro enabled Excel.

    I'm thinking that the report can be generated as part of Step 8

    Please Login or Register  to view this content.
    Immediately after
    Please Login or Register  to view this content.
    I'm thinking that we should be able to use (r, c) in Report C1, then (1, r) to put the row header in Report A1, and (c, 1) to put the column header in Report B1, then moving report down to the next line for the next data.

    It's that, or create another lookup chart with diagonal headers.

  47. #47
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Hi

    That would work.
    But it could also be easily added as the last Step in the current process, since this is how it came about.
    Sometimes it is easier to add a new step, rather than tinker with a Step that you have checked already works OK.

    I have attached an updated version to show you.
    In this version, I have removed the clutter of the [Step x] buttons on the [Master] sheet, as they have served their training purpose.

    I have also added a 'completion message' (with the time-taken) as I find that people like to know when a routine has finished, and often, how long it took.

    NOTE: For faster performance when running routines, you should close any VBA windows.

    On my system, processing the 3 data sheets and creating the [Report] took less than a second.

    zeddy
    Excel Rebel Without Applause
    Attached Files Attached Files

  48. #48
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Thank you.

    I'll take a look tonight.

  49. #49
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    That seems to be working, thanks.

    I've made a couple of "dirty" edits.

    In step11 I've added
    Please Login or Register  to view this content.
    Just to set the column alignment.

    And I've changed
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    as I'm happy with them not being red.

    It's seems to be working OK.

    Just need to work on my import script now :D
    Attached Files Attached Files

  50. #50
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Hi

    Great work!

    In my posted file, I pre-set the default column alignments on the [Report] sheet
    (by selecting each of the Report columns in turn, i.e. clicking the Excel-Frame-column-A, choosing 'centre-align', then clicking the Excel-Frame-column-B, choosing 'centre-align' etc etc etc).
    By pre-setting the alignments, I didn't need to specifically add code to do the alignments.
    But as per your coding adjustments above, you now know how to enforce your alignments via vba code.

    Originally, I too used..
    Please Login or Register  to view this content.
    ..but I changed that to the red-cell-copy method in my posted file, to show you the CopyFromHere CopyToHere method. You picked that up perfectly, and adjusted it correctly to your preferred requirement.
    Nothing 'dirty' about those changes!

    I see you have made 'indenting' adjustments to the vba code.
    Good work. This shows you are following the code OK.

    The way I write code is my preferred way, i.e. I try and keep the code as short as possible (comments in code for every single line of code preferable).
    I don't declare variables unless I really really have to (Excel can coerce as required)

    more on this in my next reply
    ..I've been summoned by the boss again

    zeddy
    Excel Cleanup Crew

  51. #51
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    No worries.

    I value your help. You have been indispensable!

    I'm just trying to work out the code that will import the individual sheets from their respective files.

    Each file has 4 sheets in, but I'm only interested in 1 of those sheets in each file.

    Unfortunately, the stem that generates the sheets appends a sheet number to the sheet name, and as they don't appear in the same order in each book, that's tricky.

    Once I've got the script that will copy the sheet to the master workbook, I also need to rename the sheet with the filename it's come from (minutes the .xls extension)

    I started a separate thread for that, but despite my promise to look at the help given, I haven't had chance... Between a 70 hour work week and SWMBO there's not a lot of time left, lol.

    Here is my other thread:

    Copying specific sheets from multiple workbooks into 1 "master" workbook.

  52. #52
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Hi

    OK, I'm going to Beirut tonight - car pickup in two hours.

    To continue where I was, the way I write code not everyone agrees with.
    The top of my vba modules have a top-heading-section something like this..
    Please Login or Register  to view this content.
    This is designed to fit on A4-paper (for UK work) when I print the code.
    For USA work, I use a different block that fits exactly on their 8.5 x 11 paper.
    This ensures my code doesn't get 'wrapped' when I print a hard copy. I hate wrapping of code.

    I use a tab-alignment character (see directly underneath the text last updated: xx-xxx-yyyy in the top-heading-block) to align my line-by-line comments.
    This forces me to use short code lines always. Short is good. Lean and mean. No baggage.

    I don't indent, because research has shown it's quicker to read code without the indents as you become more experienced. For technical documentation, I use a documentation-indenter-tool.

    I don't declare variables because it's quicker not to, and anyway, that's like listing all the words you plan to use before you start writing your book. Of course, sometimes you have no choice.
    If I'm doing life-or-death stuff, then I would use proper formal standards and use declarations etc etc etc.
    That's like Vegan VBA rather than the butchered stuff I like.

    Now, regarding your merging..
    You are saying that you are importing only a single sheet from your multi-sheet workbooks.
    The sheet you are importing from all of your source workbooks can be identified by a common pattern (ignoring UPPER/lower case text)?
    Well that seems straightforward.
    Perhaps we could do that as a STEP 0?

    Finally, regarding SWMBO: it has always been so, and be thankful for what you get. Always.

    zeddy
    Excel Non-Volatile Manager

  53. #53
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    There are 2 formats. The # represents the number of the tab in the workbook, which is included in the tab name, and varies dependant upon tab position.

    I have marked the tabs I'm interested in with ***

    1 format the tabs are

    Bbbbb_Ssssss_Aa_Lll_ddd_GGG_# ***
    Bbbbb_Ssssss_Cc_Lll_ddd_GGG_#
    Bbbbb_Rrrrrr_Aa_Lll_ddd_GGG_#
    Bbbbb_Rrrrrr_Cc_Lll_ddd_GGG_#

    The other format the tabs are

    Aaaaa_SSSSSS_Lll_ddd_GGG_# ***
    Aaaaa_RRRRRR_Lll_ddd_GGG_#
    Ccccc_SSSSSS_Lll_ddd_GGG_#
    Ccccc_RRRRRR_Lll_ddd_GGG_#

    As for the way you format code, doesn't bother me in the slightest. I find indented easier to follow, but then I'm a complete novice.

    And if you mean by declaring variable changing the "i" etc to a name, again, doesn't bother me.
    Last edited by escribblings; 11-10-2019 at 05:55 AM.

  54. #54
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    I've done it - I think - It certainly works - and now I want it to do ONE MORE THING lol

    Anyway, I've added a step 0 that deletes all sheets past "Report", and added step0A using the code that I solved in the other thread to import all the sheets, and rename them. (With the 3 test sheets we have been using that took 4-5 second just to import).

    I removed step 11 and split it between step 1 and step 8 - I really didn't see the point of looping over it again when we're already there...

    Anyway, attached is the working file - BUT REDACTED code, so it won't for for you until you create the other file - I may do this later for others as an example, but I don't have the time right now.

    Processing this and all my data files took 28 seconds, and showed 211 "errors" for me to report back to management.

    So thank you, you have been a huge help.

    Now for that one more thing...

    On each imported tab - I need to do one last check that I had forgotten about.

    For each cell with a value, it needs to check the rest of the cells in the column above AND the row to the right - if any re HIGHER, that cekk should be filled red, and the sheet listed on the report sheet in a new column.

    I've run out of time now, but I'll look at this adain later.

    zeddy3.png

  55. #55
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    OK, so I think I've done it. However, before I mark it as solved I would appreciate your eyes over it.

    I have created a small example zip, it uses a relative path, so just keep the ImportMe folder in the same directory as the Master sheet and it should work.

    On top of the changes I made above, I have also added a script (step 6A) to highlight issues on each individual sheet - it will mark any cell where any values to the right or above are higher. It also colours the tab.

    (VBA) Check cells in sheet against cells above and to the right
    Attached Files Attached Files

  56. #56
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Hi

    Back from New York, ready for Transpennine Express to Manchester at the weekend, then a couple of fortnights in Barbados.
    Looking forward to Vitamin R (rum) and Vitamin Sea.

    You have done an amazing job, considering where you started from.

    In the attached file v1e:
    I've shown a different method for step0, just to show how I would do it.
    But nothing wrong with what you did!

    Rule 1: if it works OK and does exactly what you want, then nothing wrong with that.

    For the step0A routine, I wanted to introduce you to fso, the file-scripting-object.
    It has some powerful uses and is worth having a look at.

    For example, if you want a filename without the file extension, you can just use..
    zFilename = fso.GetBaseName(zFetch) 'filename without file ext

    This works even if your filename has several 'dots' in it,
    e.g. [[email protected]]

    For the step0A routine, I also showed another method for checking for matching sheetnames.
    There is a powerful pattern-matching Like command available in VBA.
    e.g. If zShtname Like "*SSSSSS_A*" Then …

    It's worth having a look at this Like command.

    If your process takes a long time (anything more than a few seconds), then I think it's a good idea to 'refresh the display' just after you clear it, and then, freeze the display immediately again, as you continue the vba processing. This shows the User that something is 'happening' . If you leave the 'refresh' of the screen only when you have finished and done all the processing, the User might think nothing is happening after starting the process. When they see the screen clear of data, they know the screen will show new updated data when the process has completed.

    In the doAllSteps routine, I added a line of code to 'turn off' the 'green exclamation marks on the yellow diagonal row.
    These (!) indicators just alert you that there are numbers prefixed with an apostrophe in those cells.

    Also, I added a line of code to adjust the column-widths on the [Master] sheet when all processing has completed etc etc etc.

    Personally, I never put a [SOLVED] response because there will always be better solutions later from other contributors.
    I'm not sure if you can add any 'replies' to a [SOLVED] thread. Could be wrong there though.

    zzzeddy
    •Excel Station Outreach

  57. #57
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Hi

    ..and here is the attached file..
    Attached Files Attached Files

  58. #58
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    And thanks again.

    I'll take a good look at those commands.

    I need to do something else with the data as well, which will work with all 4 sheets in each data file, so that's a separate project, but this should give me a good head start.

    Thanks again for your help.

  59. #59
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Ok, so here I am now, stuck on the very last part...

    I was going to do this as a separate file, but have decided that I would like to try and include it in this one instead.

    I'm at work (again), so I'll ask you to work from the zip I posted above.

    So here's the issue. I wasn't to combine all 4 tabs into 1 sheet.

    My idea is that the cities would be spread across 2 rows and 2 columns each, giving a 2×2 grid sat each intersection.

    I figured that as Excel refers to merged cells by the to left corner cell, this should be ok to create the master sheet from, maybe using a "step = 2" in the loop, or " = columns / 2 - 1" say some point.

    I see 2 ways of doing this - rerun the script as is, THEN import the extra data, inserting rows and columns as I go. This week's require opening the files twice though.

    Or, inserting the data while reach file is open and before the master sheet is generated. My preferred option, but tricker.

    In actual fact, I don't need to import all the data, I can run an index match on the data from the first sheet to pick up static values from a list. But then I need to compare those values against the other data sheets.

    It's that, or import the data then compare against the list.

    Sorry if this it he a bit rambling.

    I'm not going to get chance to post any examples until tomorrow evening at the earliest.

  60. #60
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467
    Hi
    I'm on the Transpenine Express.
    Never replied using my phone on a train before!
    I have a solution but it's on my laptop in the luggage rack.
    I'll send it later when I'm in the hotel.
    After happy hour.

    zeddy
    Excel Travelling Solutions Unit

  61. #61
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Wow....


    Thank you.

    Enjoy happy hour.

  62. #62
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Hi

    Ooops. I exceeded my Happy hour beyond all expectation last night. I was excited about my trip today.
    As a professional, I didn't want to get done for "PUI", which I believe our friends across the pond refer to as "Posting Under the Influence".

    Also, it seems I have packed a faster laptop for my trip, so left that file on another laptop in Newcastle.
    But here's what I did..

    You've already told us that the diagonals are cities, and you don't want them sorted alphabetically in order.
    So let's suppose that this must be a delivery route. And let us suppose you have big trucks, like, say A=Articulated Lorries , and small trucks, say C=Cargo Vans. And they can be either R=Refrigerated or S=Standard.
    R=Refrigerated is more costly than S=Standard.

    Then you have four sheets for the four types: AR, CR (Refrigerated) and AS, CS (Standard)

    The data values could represent your "cost-per-mile-rate", so, in order of expense, you probably have costs in this order AR>CR>AS>CS, and increasing as you go down the diagonal of cities on the route.

    I assume the four sheets would always have the same cities on the same route, in the same order etc etc etc.
    So the four data sheets are blocks of data [AR], [CR], [AS], [CS]

    The way I 'merged' your 4 data sheets would be as follows:
    Paste all sheet data-blocks onto one sheet like this..
    [AS][CS]
    [AR][CR]
    ..and then sort.

    We are going to sort the new large combined-datablock on the new sheet by rows and by columns.
    Before we do this, we note that Excel sorts numbers before text values, so we put a zero 0 in cell [A1] on each of the data sheets (making a note of which one is which). And we have to make sure the column [A] values are numbers, not text (otherwise the sort would be like 0,1,10,11,12,..,19,2,21,22 etc etc etc).

    Also, because we will be sorting by columns, on each of the four data sheets, in row 1, we put number values starting at 0 for cell [A1], 1 for [B1], 2 for [C2] etc etc.

    To make it easier to see the results after our sorting, we should add colours to each sheet’s data block.
    On the sheet for AR , select cell [A1], press [Ctrl][a] to select the current region, and choose a dark Theme shade for the fill colour.
    Do the same for sheets CR, AS, and CS, choosing next-lighter-shade for the fill colour for each sheet’s data block.

    Make a copy of the [AS] sheet and rename it as “Merged” (saves having to adjust column widths etc).
    Now Ctrl-C copy the data-block on sheet for [AR] and paste it directly below the existing block for [AS] on sheet [Merged]
    Now Ctrl-C copy the data-block on sheet for [CS] and paste it in row 1, alongside the existing block for [AS]] on sheet [Merged].
    Now Ctrl-C copy the data-block on sheet for [CR] and paste it directly underneath the [CS] block ] on sheet [Merged]

    Now we can use Excel's sort feature. In VBA it would be..
    [a1].CurrentRegion.Sort Key1:=[a1]
    [a1].CurrentRegion.Sort Key1:=[1:1], Orientation:=xlLeftToRight

    I'll try and re-do the file, but am about to leave for the airport.
    Have a go and we can compare later?

    zeddy
    Excel Developer On Tablets

  63. #63
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    That's a good analogy.

    The format in looking at is actually

    [AS][AR]
    [CS][CR]

    But yours works just as well.

    I only need AS on the master sheet though, the rest can be kept in the data sheets.

    I'm working until 8! today, I'll try and have a look when I get home. Wife depending, obviously.

  64. #64
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467
    On the plane ready for takeoff.
    Landing in 8.5 hours or so
    Switching phone off.
    Will catch up soon.

    zeddy
    Excel On Holiday

  65. #65
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Well I've got this far and getting subscript out of range

    Please Login or Register  to view this content.
    I had to comment out the EndIf as that threw up "EndIf without block If"
    Attached Files Attached Files

  66. #66
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    I'm back at work, and forgot to bring my laptop (got a long lunch too :'( )

    Yesterday was a long day. Started work at 7:30, finished at 7:30, had dinner, walked the dog, then SWMBO wanted to go to Tesco!

    Finally got home around 11pm.

    I typed up the above while falling asleep at midnight, so there are probably done glaring errors in it. Also thought I'd posted it last night, but it was still sat in preview this morning.

    Not home till 8 again tonight.

  67. #67
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    OK, found the Subscript error

    Please Login or Register  to view this content.
    Should have been

    Please Login or Register  to view this content.
    but now it's stopped pitting the numbers across the top row!

  68. #68
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Hi

    Since arriving here there have been island-wild power cuts.
    Nothing to do with me.
    No power means no internet.
    So, had to wait for power to be restored before I could post this.
    The attached file is un updated version of yours.
    It's not finished yet, but is something to keep you interested.
    I'm a bit blitzed.
    I've been called to the bar.

    zeddy
    Excel Marvelous Molasses
    Attached Files Attached Files
    Last edited by zzzeddy; 11-19-2019 at 09:40 PM.

  69. #69
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    OK, got the numbers back - not sure why I have had to get so verbose all of a sudden - it was working last night.

    Anyway, It's merging all but Ccccc_RRRRRR - and I don't know why!

    Please Login or Register  to view this content.
    Attachment 650574
    Attached Files Attached Files

  70. #70
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Thank you, I hadn't seen your reply above when I made my last post.

    I'll take a look at that when I get in tonight.

    Thanks again.

  71. #71
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Just had a look, and that look really good.

    I'll work on importing it into the master sheet later, because I still need to tun some other checks on it.

    Thank you, I'll post how I get on later.

  72. #72
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Hi

    Well I've just had my favourite fish-of-the-day: Bob Marlin
    very nice it was too.

    I updated my file to test3.xlsm.
    This will put the City names in the first row and the first column.
    I also made the diagonal yellow.
    Have a look at the code changes made.

    zeddy
    Excel Lobster Herder
    Attached Files Attached Files

  73. #73
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Thanks, I'll take a look tonight.

  74. #74
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    I'm now trying to merge both sets of code, and I have had some success.

    Note that I have commented ou step 6a as this has been merged into one of the steps above.

    It has correctly merged the sheets in the individual data files, coloured the cells and tabs as required, and copied these into the master workbook.

    It also appears to have created the city list and diagonal on the master sheet.

    It also appears to be changing the first couple of cells in column A on the imported sheets

    But then it falls over:

    zeddy 1e(1a)1.png

    zeddy 1e(1a)2.png

    I'm sure this has to do with adjusting where the cell pointer is looking, but I can't see the wood for the trees - and I think the code is getting a bit messy, but I have no idea on how to go about streamlining it.
    Attached Files Attached Files

  75. #75
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Hi

    I will have a look tomorrow and clear the trees for you.

    zeddy
    Excel Lumberjack

  76. #76
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Thank you kindly.

  77. #77
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Hi

    In version 1e of the 'diagonal processing tool' we are essentially comparing a single 'data value' at the junction of 2 'city values', from multiple sources. So let's leave that as Version 1.

    What you are now asking is to compare four 'data values' at each junction of 2 'city values', from multiple sources.
    Let's call this Version 2.

    This is possible but would certainly require adjustments to the vba code.
    In particular, for the Version 2 [Master] sheet, we need it to look structurally like the 'merged' data sheet.

    I have done some updates to the code to generate the new 'staggered-2x2-block-diagonal' layout for the [Master] sheet, but need to run some tests first before I am satisfied with it.

    A question still remains regarding the sequence of the 'diagonal-cities' on the [Master] sheet after merging from multiple sources.

    zeddy
    Excel Decongestant Officer

  78. #78
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    I don't know if there's a misunderstanding here.

    For the master sheet, I only need the master sheet to reference AS. I don't need it to compare the other 3 cells of each matrix.

    I will run another rule against those, but they don't need to show on the master.

  79. #79
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467
    Ok, gotcha

    I'm under a palm tree on a Barbados coral sand beach.

    It's 30C and I'm feeling the breeze.
    Will get back to this later.

    zeddy
    Excel Fronds In High Places

  80. #80
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Lucky you

    Enjoy

  81. #81
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Hi

    Well here's an updated version 1f for you to check.
    I haven't tidied the code yet, I do clean-ups at the weekend.
    I might have Lion fish tonight - living dangerously.

    zeddy
    Excel Lion Fish Hunter
    Attached Files Attached Files

  82. #82
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Great, just walking the dog, but I'll check when I get in.

    About time I paid the dog tax I suppose, here's Rusty...
    Attachment 651805 Attachment 651807 Attachment 651808

  83. #83
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    That seems to be doing the trick.

    I'm just going to work in my final piece of code now and we should be done.

    Hopefully have that up for you to look at by tomorrow.

  84. #84
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    OK - I've made a few minor changes in regards to which cells I highlight on the data sheets, and the border design.

    I've added a sheet (Lookup), and adjusted the code to start at sheet 4, not sheet 3.

    Now comes the final piece of the jigsaw, for each group of 4 cells in the data sheet, I need to compare them against the lookup.

    Firstly, it needs to check if the value in AS is present in the AS column. If yes, move on, else colour.
    Secondly, if the AS value is present in the look up, it needs to check the AR, CS, & CR values in the group against those on the same line of the lookup table as AS. If they all match the relevant cells on the lookup, do nothng, else colour the cells that do not match.

    I was thinking of using INDEX and MATCH - but all the tutorials I find are about fetching a value, but I just want to look at it.

    Don't forget to put that file in the same location as the ImportMe folder (but not inside ImportMe).

    cheers

    . z Z . z Z
    Attached Files Attached Files

  85. #85
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Hi

    Got your file OK.
    It's a busy day for me today, so will look at this when I get the chance.

    zeddy
    Excel Travelling Wilbury

    ..and I didn't get to see your dog

  86. #86
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    No worries.


    Did the attachment links not work?

    I posted from my mobile, but if you click on the attachments above, you should see him.

  87. #87
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467
    Hi
    I clicked the links but it said invalid attachment.
    A beach dog has adopted us.
    My wife has been making corned beef sandwiches for the dog all week. It is now getting Waitrose beef and chicken chunks in rich gravy. Yes, you can buy Waitrose food here at great imported expense.
    This dog sunbathes on its back with four legs in the air.
    It is a sight to behold.

    zeddy
    Excel Hound Feeder

  88. #88
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"


  89. #89
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    When he was smaller

    20180414_142256.jpg

  90. #90
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    With me at work

    20181210_231007.jpg

  91. #91
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    He also sleeps in strange positions.

    We've nicknamed him incorrectly Assembled Ikea Dog

    20190724_161134.jpg

  92. #92
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Back to the grind, I've added the following code in the end of Step0A

    Please Login or Register  to view this content.
    But that is breaking the do loop.

    zeddy 1g.png

    Attached v1g
    Attached Files Attached Files
    Last edited by escribblings; 11-30-2019 at 05:22 PM. Reason: Corrected code / uploaded corrected file

  93. #93
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Ok, fixed that, I'd used Exit For instead of Next, Doh!

    Just trying to work through the runtime errors now.

  94. #94
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    OK, this has me stumped!

    zeddy 1gb1.png

    zeddy 1gb2.png
    Attached Files Attached Files

  95. #95
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Wondering if I should change

    Please Login or Register  to view this content.
    To

    Please Login or Register  to view this content.
    Won't be able to test that until this evening though...

  96. #96
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Hi

    You have a very nice dog!

    Been manic here. Lots of celebrations etc etc etc

    Haven't had a chance to review your last posted file yet.
    In the meantime, I had been working on v1g (attached).

    There are many ways of doing what you asked for.
    I approached it by adding some cells and formulas to do the validation checks on your sheet [Lookup].
    Doing the checks on the sheet rather than directly in VBA means you can amend the validation rules on the sheet (which is easier)

    see what you think, and let me know

    zeddy
    Excel Adjustments Bureau
    Attached Files Attached Files

  97. #97
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Ok, will check that out later.

    Happy celebrating.

  98. #98
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    OK - that's working where my VBA doesn't, sp that's good.

    I've change how some of the cells are highlighted, and I'm having an issue with the borders...

    I'm using .BorderAround on each 2x2 range, but for some of these it'snot changing all the outside borders!

    zeddy 1h.png
    Attached Files Attached Files

  99. #99
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Hi

    That's good - we're down to the cosmetics then.
    Will have a look later - off to do nothing on the beach, and then rest afterwards.

    zeddy
    Excel Border Control Unit

  100. #100
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102
    Thanks, enjoy the nothingness.

  101. #101
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Hi

    OK, it seems that the Excel Range.BorderAround Method doesn't seem to work properly here.
    Might be an Excel bug there!

    I added some checks to the [Lookup] sheet to simplify things. Have a look at the changes.

    I changed the routine checkLookupRates() so that if any invalid entry is detected within the 2x2 blocks, the top, left, bottom and right edges are now each set separately to thick red lines.

    This slows things up a bit (on my system, the process to 27 seconds to complete).

    Perhaps someone else might explain why the BorderAround method is giving problems.
    I haven't concentrated on optimising - just getting the results you wanted. Hopefully.

    zeddy
    Excel Electric Border Fence Fixer
    Attached Files Attached Files

  102. #102
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Thanks, will take a look.

  103. #103
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    OK, that's excellent - while faster is better, speed is the least important aspect of this ATM.

    I've been thrown a curveball today - the AR CS and CR lookups can potentially have TWO different values against the AS value.

    Obviously I can simply add columns into the lookup table - and I'm gussing I would need to put an OR into the code...

    I've inserted a column, added 1 value, added a lookup to that colum in K6 and then entered the following in K8 - does this look right? I won't be able to truly test this until I get the new data files from work...

    Please Login or Register  to view this content.
    zeddy 1ia.png
    Attached Files Attached Files

  104. #104
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Hi

    You are on the right track.
    This is fairly simple to implement (because our design strategy is so good)..

    I added the extra columns for the second-values for CS and CR in the lookup block.
    I simplified the check-validation formulas in range [K8:M8] on the sheet [Lookup]

    The code doesn't need any changes.

    The rule is:
    If the AS value isn't found in column [A] of the [Lookup] sheet, then the corresponding values for AR, CS, and CR cannot be cross-referenced at all, regardless, so are all treated as 'invalid'.

    If the AS values is found in column [A], then we fetch the corresponding pairs of values (blue cells).
    We then just check for a respective-match in these values pairs.

    zeddy
    Excel Curve Ball Hitter
    Attached Files Attached Files

  105. #105
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Great, I'll take another look when I get in

  106. #106
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    It's working fine with the test files, but falling over with the live data.

    I'm sending you a PM as I can't have the live data publicly vailable.

  107. #107
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Hi

    Check there are no hidden worksheets in your live data.
    This could throw off the vba.
    Make sure all sheets are visible (Format>Sheet Unhide ), and get rid of any sheets you don't need.

    zeddy

  108. #108
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    I'll have a go.

    I've just had a thought though, and this might or might not help...

    All the cities are technically unique, what is the purpose of numbering column A? Why can it not just be populated with the cities!?

    They're not being sorted out of the other they're currently in and they're a text field (there's more than just the number in brackets in the live version).

  109. #109
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Hi

    what is the purpose of numbering column A?
    The numbers represent the [Master] sheet row number for that particular city on the diagonal.
    We need to find the matching row for each city when processing the data sheets.
    After the [Master] data sheet has been updated, we could put the city names in column [A] on the data sheets as we no longer require these 'matching row numbers'.
    However, they were left in so that we could check that the correct blocks for each city were being processed correctly.

    zeddy
    Excel Corrections Officer

    PS: I replied to your PM

  110. #110
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Thank you.

    As you will see from my mail, I got it working.

    I must have inadvertently added or deleted something that was enough to skew the output without breaking the code.

    I downloaded and tries again and got it working.

    But now I need to turn that 2×2 into a 3×2

    [AS][AR]
    [CS][CR]
    [SS][SR]

    I'm working with live data now, so you have all the necessary information.

  111. #111
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Hi

    Mail? didn't see any yet - please check spelling.

    so it's Carpe diem..
    ..off to eat catch of the day

    zeddy
    Excel Fishermans Friend

  112. #112
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Hi

    I found it!
    It was filed under J for General.

    I will look at this as soon as possible.

    zeddy
    Excel Hungry Hunter

  113. #113
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Haha. Thank you.

  114. #114
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Any luck yet?

  115. #115
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467
    Quote Originally Posted by escribblings View Post
    Any luck yet?
    If it wasn't for bad luck I wouldn't have any at all.
    I have to start packing up from the beach soon.
    Can't do Excel in blazing sunshine.
    But I'm still thinking about it.

    zeddy
    Excel Lunchtime

  116. #116
    Forum Contributor
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    102

    Re: Searching and comparing data from multiple sources with non-contiguous "headers"

    Hey, that's my line

    However, I appreciate all you've done, especially as you're on holiday.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Sum data from multiple sources based on rows and column headers
    By rogclark in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-11-2019, 11:32 AM
  2. Searching through a non-contiguous range
    By VBAClueless in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-14-2016, 09:41 AM
  3. [SOLVED] Copying (non-contiguous) headers to next sheet: errors with Union method
    By k64 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-29-2013, 12:07 PM
  4. [SOLVED] How to delete multiple contiguous (and/or) non-contiguous rows, in an excel table
    By jimmalk in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-03-2012, 05:48 AM
  5. Searching non consecutive or contiguous words
    By wvollbon in forum Excel General
    Replies: 0
    Last Post: 01-31-2012, 07:35 PM
  6. Replies: 0
    Last Post: 12-12-2011, 07:27 PM
  7. searching for data from two sources
    By duckhunter2847 in forum Excel General
    Replies: 5
    Last Post: 05-15-2009, 02:27 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1