Formula to copy Customer Code to appropriate cells [SOLVED]

[MnhPhm]

Hi all,


I wouldn't call myself proficient in Excel but I'm familiar with basic formulas, filters, conditional formatting, etc. This dataset that I'm trying to analyze (picture) has a structure that I'm trying to manipulate to make it easier to analyze.


i'm trying to calculate the average spend per customer, average spend per transaction, average transaction/month (which can include multiple products at a time) so on and so forth to find behavioral patterns that can turn to actionable marketing insight.


I don't know if I'm going about it right but as a first step, I'm struggling to come up with formula that would copy the appropriate customer code to fill each row. For example, I want to fill B3:B5 with the code in B2, B7:B8 with the code in B6, so on and so forth. After that, I plan to use pivot table to maybe find the above figures.


I'm brand new to data mining this type of sales data so please help me. Any advice on tricks, methods would be much appreciated.


http://tinypic.com/r/sljwqt/5

 

[NARAYANK991]

Hi ,


You can see the technique explained here :


http://www.techrepublic.com/blog/msoffice/quickly-fill-blank-cells-in-excel/


However , you should remember something that I'll explain below ; suppose you have data as follows :

[pre]

Data1

Data2

Data3

[/pre]
where there are blank cells after Data1 , Data2 and Data3 , which you need to fill up by repeating the text from the cell above ; even if you follow the procedure given in the link , the blank cells after Data3 will not be filled up.


In order to do that , put a space in the last cell in the range , before you follow the procedure ; this ensures that the blank cells between Data3 and the cell which has the space , are also filled up.


Narayan

 

[MnhPhm]

Hi Narayan,


Thanks, I followed both your instruction on the final blank cell and the link's technique. However, it only worked with the first data I needed to repeat. Going back to your example


Data1


Data2


Data3


After I did [Command + Enter] only the blank cells after Data1 is filled with Data1 values, blank cells after Data2 and Data3 remain the same. Any idea why and how I could fix it?

 

[NARAYANK991]

Hi ,


The keys to be pressed together are CTRL and ENTER ; this works on my PC , with Excel 2007.


Let me summarise the procedure as follows :


1. Suppose the three filled cells are B54 , B57 and B60.


2. Suppose you want the following cells filled in : B55 and B56 , B58 and B59 , B61 , B62 , B63 , B64.


3. Enter a space in cell B65.


4. Place the cursor in B54 , and select the range B54:B65


5. Press F5 or CTRL G to initiate the GoTo feature.


6. Click on Special


7. Select Blanks


8. Click on OK


9. All the required blank cells will be selected , and the cursor will be in cell B55


10. Press the = key and press the UP arrow to get =B54 in the cell


11. Press the CTRL ENTER keys together


12. All the blank cells will be populated.


Narayan

 

[MnhPhm]

Yay, that definitely worked. Thank you so much Narayan!! that was extremely helpful!

 

[MnhPhm]

Note to others: Sometimes, the equivalent of CTRL on a Mac is not the Command button. I learned my lesson today!