Posts by JamJ

  • Creating customer frequency report

    I have two sheets.

    Sheet 1 has listing of individual transactions with columns A date, B name, C email and D value columns

    Sheet 2 is a listing (in column A) of unique values extracted column C in Sheet 1) + a range of columns (B......AW) each with a date range in title (Example column B has date 08/03/2021 in cell B1 and "14/03/2021" in cell B2).


    What I need (and after a couple of hours trying clearly beyond my skills, although I may kick myself I was circling around the solution)


    In Sheet 2, in each cell in columns (B....AW), return a "1" or a "0") if


    - (1) value of A2 in Sheet 2 is present in column C in Sheet 1, and

    - (2) where it is present the date in column A on same row is => that B1 and =< B2)


    In words, I would like to know, the weeks in which each email in Column A in Sheet 2 made a purchase or less.

    Thanks

  • Extracting Post Code and Locality

    Good Morning All


    I have the a series of addresses each in a single field as follows


    [TABLE="width: 403"]

    [tr]


    [td]

    75 r Léon Boyer 37000 TOURS

    [/td]


    [/tr]


    [tr]


    [td]

    La Fredonnerie 37330 MARCILLY SUR MAULNE

    [/td]


    [/tr]


    [tr]


    [td]

    La Rabottière 37150 LUZILLE

    [/td]


    [/tr]


    [tr]


    [td]

    8 Bourg Neuf 37340 RILLE

    [/td]


    [/tr]


    [tr]


    [td]

    45 r Hospitalité 37000 TOURS

    [/td]


    [/tr]


    [tr]


    [td]

    23 pl Foire Le Roi 37000 TOURS

    [/td]


    [/tr]


    [tr]


    [td]

    15 Bis r Espérance 37600 BEAULIEU LES LOCHES




    I would like to extract the Post Code and the Locaility (as marked in red in the last address).


    I tried using the RIGHT and FIND functions but I have encountered two problems


    1. It does not seem to work as (for example if you limit the function to the first space) rom the 1st address it extracted "RS". It may be something in the format but cannot find anything wrong. I also checked the cell using the data to column wizard choosing space as a delimiter and "TOURS was extracted as one word.


    2. Given the type of locaity names the function which would apply for the 1st address will not work for the 2nd.



    Anyone can help.


    JamJ



    PS. Alternatively I can settle for a solution to inserting a hyphen before the postcode, and after use the data to column wizard.

    [/td]


    [/tr]


    [/TABLE]

  • Search in cell and return value

    Hello All,


    I'm new to this forum.


    I have two excel sheets,


    Sheet A contains a list consisting of multiple columns with one of these containing a full postal address (part of which is a postcode)


    Sheet B contains a list on two columns with a one column containing postcodes and the other column with an "other value" (which varies for each postcode)



    In a new column on sheet A, I would like excel to lookup each row in the address column on sheet A see if it contains one of the postcodes in sheet B and return the corresponding "other value" from sheet B.


    Hope to have been clear enough.


    Thanks and Regards
    JamJ