Filling empty cells with data from previous non-blank cell ?

[mystic2k]

Hey there,

I'm stuck with this one... been bugging me all morning and I think I've reached the limits of my excel knowledge

Basically I have a download of SAP access rights. The first few columns contain informations about the user account (user name, first name, last name, user group...), and the following columns contain the SAP access rights information.

Each piece of SAP access is given on a separate row, any most individuals have several of them. But the problem is that for the information in the first few columns, it's only present on the first row for a given individual. Columns below are blank, unless the information is different from the row 1 for this given individual.

This is a pain as it prevents me from using Autofilter effectively on this data. Can anyone suggest a way of automating the process of filling the empty cells with the data from the first non-blank cell going upwards ?

A picture is better than a thousand words, so here's the data as I receive it :

<table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:142px;" /><col style="width:111px;" /><col style="width:110px;" /><col style="width:113px;" /><col style="width:98px;" /><col style="width:144px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >User Name (T-Number)</td><td >First Name (SAP)</td><td >Last Name (SAP)</td><td >User Group</td><td >Source System</td><td >Roles</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >AA1696</td><td >JOHN</td><td >SMITH</td><td >ESSUSER</td><td >NP1400</td><td >ZSAP_ESSUSER</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td >SMITH-J</td><td >USGBBROOF&A</td><td >G4P470</td><td >ZGBSR05L1068666016</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td > </td><td > </td><td > </td><td >ZXXSB01</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td > </td><td > </td><td > </td><td >ZXXSR05</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td > </td><td > </td><td > </td><td >ZXXSR11MISC</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >AA2678</td><td >JACK</td><td >MCDONALD</td><td >USXXGGGGINVA</td><td >F6P430</td><td >ZXXSB01</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td><td > </td><td >XXXXOITV2</td><td >NP1400</td><td >ZGC10XX</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td > </td><td > </td><td > </td><td > </td><td >ZOS10XX</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td > </td><td > </td><td > </td><td > </td><td >ZSAP_ESSUSER</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td > </td><td >MCDONALD-JD</td><td >LVXXGGGGLOCK</td><td >G4P470</td><td >ZXXGL42</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td > </td><td > </td><td > </td><td > </td><td >ZXXSB01</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >AA9378</td><td >MICHAEL</td><td >JOHNSON</td><td >ESSUSER</td><td >NP1400</td><td >ZSAP_ESSUSER</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td >AB2864</td><td >TIM</td><td >ALLEN</td><td >XXXXOITV2</td><td >NP1400</td><td >ZGC10XX</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td > </td><td > </td><td > </td><td > </td><td > </td><td >ZOS10XX</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td > </td><td > </td><td > </td><td > </td><td > </td><td >ZSAP_ESSUSER</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td >AC6158</td><td >FRED</td><td >GOMEZ</td><td >XXXXGCSMGR</td><td >NP1400</td><td >ZGC10XX</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td > </td><td > </td><td > </td><td > </td><td > </td><td >ZOS10XX</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td > </td><td > </td><td > </td><td > </td><td > </td><td >ZSAP_ESSUSER</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td > </td><td > </td><td >GOMEZ-F-14</td><td >USCHGENEMSA</td><td >G4P470</td><td >ZXXSB01</td></tr></table>

And here's what I need (the yellow cells being the "new" data) :


<table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:142px;" /><col style="width:111px;" /><col style="width:110px;" /><col style="width:113px;" /><col style="width:98px;" /><col style="width:144px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td >User Name (T-Number)</td><td >First Name (SAP)</td><td >Last Name (SAP)</td><td >User Group</td><td >Source System</td><td >Roles</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td >AA1234</td><td >JOHN</td><td >SMITH</td><td >ESSUSER</td><td >NP1400</td><td >ZSAP_ESSUSER</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td style="background-color:#ffff00; ">AA1234</td><td style="background-color:#ffff00; ">JOHN</td><td >SMITH-J</td><td >USGBBROOF&A</td><td >G4P470</td><td >ZGBSR05L1068666016</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >28</td><td style="background-color:#ffff00; ">AA1234</td><td style="background-color:#ffff00; ">JOHN</td><td style="background-color:#ffff00; ">SMITH-J</td><td style="background-color:#ffff00; ">USGBBROOF&A</td><td style="background-color:#ffff00; ">G4P470</td><td >ZXXSB01</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >29</td><td style="background-color:#ffff00; ">AA1234</td><td style="background-color:#ffff00; ">JOHN</td><td style="background-color:#ffff00; ">SMITH-J</td><td style="background-color:#ffff00; ">USGBBROOF&A</td><td style="background-color:#ffff00; ">G4P470</td><td >ZXXSR05</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >30</td><td style="background-color:#ffff00; ">AA1234</td><td style="background-color:#ffff00; ">JOHN</td><td style="background-color:#ffff00; ">SMITH-J</td><td style="background-color:#ffff00; ">USGBBROOF&A</td><td style="background-color:#ffff00; ">G4P470</td><td >ZXXSR11MISC</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >31</td><td >AA5678</td><td >JACK</td><td >MCDONALD</td><td >USXXGGGGINVA</td><td >F6P430</td><td >ZXXSB01</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >32</td><td style="background-color:#ffff00; ">AA5678</td><td style="background-color:#ffff00; ">JACK</td><td style="background-color:#ffff00; ">MCDONALD</td><td >XXXXOITV2</td><td >NP1400</td><td >ZGC10XX</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >33</td><td style="background-color:#ffff00; ">AA5678</td><td style="background-color:#ffff00; ">JACK</td><td style="background-color:#ffff00; ">MCDONALD</td><td style="background-color:#ffff00; ">XXXXOITV2</td><td style="background-color:#ffff00; ">NP1400</td><td >ZOS10XX</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >34</td><td style="background-color:#ffff00; ">AA5678</td><td style="background-color:#ffff00; ">JACK</td><td style="background-color:#ffff00; ">MCDONALD</td><td style="background-color:#ffff00; ">XXXXOITV2</td><td style="background-color:#ffff00; ">NP1400</td><td >ZSAP_ESSUSER</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >35</td><td style="background-color:#ffff00; ">AA5678</td><td style="background-color:#ffff00; ">JACK</td><td >MCDONALD-JD</td><td >LVXXGGGGLOCK</td><td >G4P470</td><td >ZXXGL42</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >36</td><td style="background-color:#ffff00; ">AA5678</td><td style="background-color:#ffff00; ">JACK</td><td style="background-color:#ffff00; ">MCDONALD-JD</td><td style="background-color:#ffff00; ">LVXXGGGGLOCK</td><td style="background-color:#ffff00; ">G4P470</td><td >ZXXSB01</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >37</td><td >AA9876</td><td >MICHAEL</td><td >JOHNSON</td><td >ESSUSER</td><td >NP1400</td><td >ZSAP_ESSUSER</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >38</td><td >AB1234</td><td >TIM</td><td >ALLEN</td><td >XXXXOITV2</td><td >NP1400</td><td >ZGC10XX</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >39</td><td style="background-color:#ffff00; ">AB1234</td><td style="background-color:#ffff00; ">TIM</td><td style="background-color:#ffff00; ">ALLEN</td><td style="background-color:#ffff00; ">XXXXOITV2</td><td style="background-color:#ffff00; ">NP1400</td><td >ZOS10XX</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >40</td><td style="background-color:#ffff00; ">AB1234</td><td style="background-color:#ffff00; ">TIM</td><td style="background-color:#ffff00; ">ALLEN</td><td style="background-color:#ffff00; ">XXXXOITV2</td><td style="background-color:#ffff00; ">NP1400</td><td >ZSAP_ESSUSER</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >41</td><td >AC5678</td><td >FRED</td><td >GOMEZ</td><td >XXXXGCSMGR</td><td >NP1400</td><td >ZGC10XX</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >42</td><td style="background-color:#ffff00; ">AC5678</td><td style="background-color:#ffff00; ">FRED</td><td style="background-color:#ffff00; ">GOMEZ</td><td style="background-color:#ffff00; ">XXXXGCSMGR</td><td style="background-color:#ffff00; ">NP1400</td><td >ZOS10XX</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >43</td><td style="background-color:#ffff00; ">AC5678</td><td style="background-color:#ffff00; ">FRED</td><td style="background-color:#ffff00; ">GOMEZ</td><td style="background-color:#ffff00; ">XXXXGCSMGR</td><td style="background-color:#ffff00; ">NP1400</td><td >ZSAP_ESSUSER</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >44</td><td style="background-color:#ffff00; ">AC5678</td><td style="background-color:#ffff00; ">FRED</td><td >GOMEZ-F-14</td><td >USCHGENEMSA</td><td >G4P470</td><td >ZXXSB01</td></tr></table>

Thanks in advance for any suggestions !!

Laurent

 

[stanleydgromjr]

Laurent,

Can you re-post your screenshot graphics with one of the below products. (with the below products we can copy the data for testing).


[url]http://www.puremis.net/excel/downloads.shtml
[/url]

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4.

[url]http://www.excel-jeanie-html.de/index.php?f=1
[/url]


Have a great day,
Stan

 

[mystic2k]

Sure, here goes :

Here's the data as I receive it :

<table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:142px;" /><col style="width:111px;" /><col style="width:110px;" /><col style="width:113px;" /><col style="width:98px;" /><col style="width:144px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >User Name (T-Number)</td><td >First Name (SAP)</td><td >Last Name (SAP)</td><td >User Group</td><td >Source System</td><td >Roles</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >AA1696</td><td >JOHN</td><td >SMITH</td><td >ESSUSER</td><td >NP1400</td><td >ZSAP_ESSUSER</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td >SMITH-J</td><td >USGBBROOF&A</td><td >G4P470</td><td >ZGBSR05L1068666016</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td > </td><td > </td><td > </td><td >ZXXSB01</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td > </td><td > </td><td > </td><td >ZXXSR05</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td > </td><td > </td><td > </td><td >ZXXSR11MISC</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >AA2678</td><td >JACK</td><td >MCDONALD</td><td >USXXGGGGINVA</td><td >F6P430</td><td >ZXXSB01</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td><td > </td><td >XXXXOITV2</td><td >NP1400</td><td >ZGC10XX</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td > </td><td > </td><td > </td><td > </td><td >ZOS10XX</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td > </td><td > </td><td > </td><td > </td><td >ZSAP_ESSUSER</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td > </td><td >MCDONALD-JD</td><td >LVXXGGGGLOCK</td><td >G4P470</td><td >ZXXGL42</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td > </td><td > </td><td > </td><td > </td><td >ZXXSB01</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >AA9378</td><td >MICHAEL</td><td >JOHNSON</td><td >ESSUSER</td><td >NP1400</td><td >ZSAP_ESSUSER</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td >AB2864</td><td >TIM</td><td >ALLEN</td><td >XXXXOITV2</td><td >NP1400</td><td >ZGC10XX</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td > </td><td > </td><td > </td><td > </td><td > </td><td >ZOS10XX</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td > </td><td > </td><td > </td><td > </td><td > </td><td >ZSAP_ESSUSER</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td >AC6158</td><td >FRED</td><td >GOMEZ</td><td >XXXXGCSMGR</td><td >NP1400</td><td >ZGC10XX</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td > </td><td > </td><td > </td><td > </td><td > </td><td >ZOS10XX</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td > </td><td > </td><td > </td><td > </td><td > </td><td >ZSAP_ESSUSER</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td > </td><td > </td><td >GOMEZ-F-14</td><td >USCHGENEMSA</td><td >G4P470</td><td >ZXXSB01</td></tr></table>

And here's what I need (yellow cells being the "new" data obviously) :


<table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:142px;" /><col style="width:111px;" /><col style="width:110px;" /><col style="width:113px;" /><col style="width:98px;" /><col style="width:144px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td >User Name (T-Number)</td><td >First Name (SAP)</td><td >Last Name (SAP)</td><td >User Group</td><td >Source System</td><td >Roles</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td >AA1234</td><td >JOHN</td><td >SMITH</td><td >ESSUSER</td><td >NP1400</td><td >ZSAP_ESSUSER</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td style="background-color:#ffff00; ">AA1234</td><td style="background-color:#ffff00; ">JOHN</td><td >SMITH-J</td><td >USGBBROOF&A</td><td >G4P470</td><td >ZGBSR05L1068666016</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >28</td><td style="background-color:#ffff00; ">AA1234</td><td style="background-color:#ffff00; ">JOHN</td><td style="background-color:#ffff00; ">SMITH-J</td><td style="background-color:#ffff00; ">USGBBROOF&A</td><td style="background-color:#ffff00; ">G4P470</td><td >ZXXSB01</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >29</td><td style="background-color:#ffff00; ">AA1234</td><td style="background-color:#ffff00; ">JOHN</td><td style="background-color:#ffff00; ">SMITH-J</td><td style="background-color:#ffff00; ">USGBBROOF&A</td><td style="background-color:#ffff00; ">G4P470</td><td >ZXXSR05</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >30</td><td style="background-color:#ffff00; ">AA1234</td><td style="background-color:#ffff00; ">JOHN</td><td style="background-color:#ffff00; ">SMITH-J</td><td style="background-color:#ffff00; ">USGBBROOF&A</td><td style="background-color:#ffff00; ">G4P470</td><td >ZXXSR11MISC</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >31</td><td >AA5678</td><td >JACK</td><td >MCDONALD</td><td >USXXGGGGINVA</td><td >F6P430</td><td >ZXXSB01</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >32</td><td style="background-color:#ffff00; ">AA5678</td><td style="background-color:#ffff00; ">JACK</td><td style="background-color:#ffff00; ">MCDONALD</td><td >XXXXOITV2</td><td >NP1400</td><td >ZGC10XX</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >33</td><td style="background-color:#ffff00; ">AA5678</td><td style="background-color:#ffff00; ">JACK</td><td style="background-color:#ffff00; ">MCDONALD</td><td style="background-color:#ffff00; ">XXXXOITV2</td><td style="background-color:#ffff00; ">NP1400</td><td >ZOS10XX</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >34</td><td style="background-color:#ffff00; ">AA5678</td><td style="background-color:#ffff00; ">JACK</td><td style="background-color:#ffff00; ">MCDONALD</td><td style="background-color:#ffff00; ">XXXXOITV2</td><td style="background-color:#ffff00; ">NP1400</td><td >ZSAP_ESSUSER</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >35</td><td style="background-color:#ffff00; ">AA5678</td><td style="background-color:#ffff00; ">JACK</td><td >MCDONALD-JD</td><td >LVXXGGGGLOCK</td><td >G4P470</td><td >ZXXGL42</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >36</td><td style="background-color:#ffff00; ">AA5678</td><td style="background-color:#ffff00; ">JACK</td><td style="background-color:#ffff00; ">MCDONALD-JD</td><td style="background-color:#ffff00; ">LVXXGGGGLOCK</td><td style="background-color:#ffff00; ">G4P470</td><td >ZXXSB01</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >37</td><td >AA9876</td><td >MICHAEL</td><td >JOHNSON</td><td >ESSUSER</td><td >NP1400</td><td >ZSAP_ESSUSER</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >38</td><td >AB1234</td><td >TIM</td><td >ALLEN</td><td >XXXXOITV2</td><td >NP1400</td><td >ZGC10XX</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >39</td><td style="background-color:#ffff00; ">AB1234</td><td style="background-color:#ffff00; ">TIM</td><td style="background-color:#ffff00; ">ALLEN</td><td style="background-color:#ffff00; ">XXXXOITV2</td><td style="background-color:#ffff00; ">NP1400</td><td >ZOS10XX</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >40</td><td style="background-color:#ffff00; ">AB1234</td><td style="background-color:#ffff00; ">TIM</td><td style="background-color:#ffff00; ">ALLEN</td><td style="background-color:#ffff00; ">XXXXOITV2</td><td style="background-color:#ffff00; ">NP1400</td><td >ZSAP_ESSUSER</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >41</td><td >AC5678</td><td >FRED</td><td >GOMEZ</td><td >XXXXGCSMGR</td><td >NP1400</td><td >ZGC10XX</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >42</td><td style="background-color:#ffff00; ">AC5678</td><td style="background-color:#ffff00; ">FRED</td><td style="background-color:#ffff00; ">GOMEZ</td><td style="background-color:#ffff00; ">XXXXGCSMGR</td><td style="background-color:#ffff00; ">NP1400</td><td >ZOS10XX</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >43</td><td style="background-color:#ffff00; ">AC5678</td><td style="background-color:#ffff00; ">FRED</td><td style="background-color:#ffff00; ">GOMEZ</td><td style="background-color:#ffff00; ">XXXXGCSMGR</td><td style="background-color:#ffff00; ">NP1400</td><td >ZSAP_ESSUSER</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >44</td><td style="background-color:#ffff00; ">AC5678</td><td style="background-color:#ffff00; ">FRED</td><td >GOMEZ-F-14</td><td >USCHGENEMSA</td><td >G4P470</td><td >ZXXSB01</td></tr></table>

Thanks in advance for your help !!

Laurent

 

[Richard Schollar]

Hi Laurent

Select the cells including the blanks (and populated cells) and go Edit>Goto>Special Button>select blanks and click OK. Immediately next, press '=' (w/o quotes) and the Up arrow key and then confirm with Ctrl+Enter. Then copy and paste values on all the cells to fix them.

 

[Domenic]

Try the following...

1) Select A2:F20

2) Edit > Go To > Special > Blanks > Ok

3) Press =

4) Press the Up Arrow

5) Press CONTROL+ENTER

Hope this helps!

 

[mystic2k]

Wow... !!! The sheer amount of functionalities offered by Excel never ceases to amaze me, and the knowledge/helpfulness of the people on this board even more so !

Thanks for the ultra-fast reply guys (still can't believe I got 2 correct answers within 7 minutes of posting the updated "screenshots").

Laurent

 

[stanleydgromjr]

Domenic, RichardSchollar,

What a great site for learning. Thanks.

Laurent,

Excel Workbook
	A	B	C	D	E	F
1	User Name (T-Number)	First Name (SAP)	Last Name (SAP)	User Group	Source System	Roles
2	AA1234	JOHN	SMITH	ESSUSER	NP1400	ZSAP_ESSUSER
3			SMITH-J	USGBBROOF&A	G4P470	ZGBSR05L1068666016
4						ZXXSB01
5						ZXXSR05
6						ZXXSR11MISC
7	AA5678	JACK	MCDONALD	USXXGGGGINVA	F6P430	ZXXSB01
8				XXXXOITV2	NP1400	ZGC10XX
9						ZOS10XX
10						ZSAP_ESSUSER
11			MCDONALD-JD	LVXXGGGGLOCK	G4P470	ZXXGL42
12						ZXXSB01
13	AA9876	MICHAEL	JOHNSON	ESSUSER	NP1400	ZSAP_ESSUSER
14	AB1234	TIM	ALLEN	XXXXOITV2	NP1400	ZGC10XX
15						ZOS10XX
16						ZSAP_ESSUSER
17	AC5678	FRED	GOMEZ	XXXXGCSMGR	NP1400	ZGC10XX
18						ZOS10XX
19						ZSAP_ESSUSER
20			GOMEZ-F-14	USCHGENEMSA	G4P470	ZXXSB01
Sheet1

Excel Workbook
	A	B	C	D	E	F
1	User Name (T-Number)	First Name (SAP)	Last Name (SAP)	User Group	Source System	Roles
2	AA1234	JOHN	SMITH	ESSUSER	NP1400	ZSAP_ESSUSER
3	AA1234	JOHN	SMITH-J	USGBBROOF&A	G4P470	ZGBSR05L1068666016
4	AA1234	JOHN	SMITH-J	USGBBROOF&A	G4P470	ZXXSB01
5	AA1234	JOHN	SMITH-J	USGBBROOF&A	G4P470	ZXXSR05
6	AA1234	JOHN	SMITH-J	USGBBROOF&A	G4P470	ZXXSR11MISC
7	AA5678	JACK	MCDONALD	USXXGGGGINVA	F6P430	ZXXSB01
8	AA5678	JACK	MCDONALD	XXXXOITV2	NP1400	ZGC10XX
9	AA5678	JACK	MCDONALD	XXXXOITV2	NP1400	ZOS10XX
10	AA5678	JACK	MCDONALD	XXXXOITV2	NP1400	ZSAP_ESSUSER
11	AA5678	JACK	MCDONALD-JD	LVXXGGGGLOCK	G4P470	ZXXGL42
12	AA5678	JACK	MCDONALD-JD	LVXXGGGGLOCK	G4P470	ZXXSB01
13	AA9876	MICHAEL	JOHNSON	ESSUSER	NP1400	ZSAP_ESSUSER
14	AB1234	TIM	ALLEN	XXXXOITV2	NP1400	ZGC10XX
15	AB1234	TIM	ALLEN	XXXXOITV2	NP1400	ZOS10XX
16	AB1234	TIM	ALLEN	XXXXOITV2	NP1400	ZSAP_ESSUSER
17	AC5678	FRED	GOMEZ	XXXXGCSMGR	NP1400	ZGC10XX
18	AC5678	FRED	GOMEZ	XXXXGCSMGR	NP1400	ZOS10XX
19	AC5678	FRED	GOMEZ	XXXXGCSMGR	NP1400	ZSAP_ESSUSER
20	AC5678	FRED	GOMEZ-F-14	USCHGENEMSA	G4P470	ZXXSB01
Sheet1

Excel Workbook
	A	B	C	D	E	F
1	User Name (T-Number)	First Name (SAP)	Last Name (SAP)	User Group	Source System	Roles
2	AA1234	JOHN	SMITH	ESSUSER	NP1400	ZSAP_ESSUSER
3	AA1234	JOHN	SMITH-J	USGBBROOF&A	G4P470	ZGBSR05L1068666016
4	AA1234	JOHN	SMITH-J	USGBBROOF&A	G4P470	ZXXSB01
5	AA1234	JOHN	SMITH-J	USGBBROOF&A	G4P470	ZXXSR05
6	AA1234	JOHN	SMITH-J	USGBBROOF&A	G4P470	ZXXSR11MISC
7	AA5678	JACK	MCDONALD	USXXGGGGINVA	F6P430	ZXXSB01
8	AA5678	JACK	MCDONALD	XXXXOITV2	NP1400	ZGC10XX
9	AA5678	JACK	MCDONALD	XXXXOITV2	NP1400	ZOS10XX
10	AA5678	JACK	MCDONALD	XXXXOITV2	NP1400	ZSAP_ESSUSER
11	AA5678	JACK	MCDONALD-JD	LVXXGGGGLOCK	G4P470	ZXXGL42
12	AA5678	JACK	MCDONALD-JD	LVXXGGGGLOCK	G4P470	ZXXSB01
13	AA9876	MICHAEL	JOHNSON	ESSUSER	NP1400	ZSAP_ESSUSER
14	AB1234	TIM	ALLEN	XXXXOITV2	NP1400	ZGC10XX
15	AB1234	TIM	ALLEN	XXXXOITV2	NP1400	ZOS10XX
16	AB1234	TIM	ALLEN	XXXXOITV2	NP1400	ZSAP_ESSUSER
17	AC5678	FRED	GOMEZ	XXXXGCSMGR	NP1400	ZGC10XX
18	AC5678	FRED	GOMEZ	XXXXGCSMGR	NP1400	ZOS10XX
19	AC5678	FRED	GOMEZ	XXXXGCSMGR	NP1400	ZSAP_ESSUSER
20	AC5678	FRED	GOMEZ-F-14	USCHGENEMSA	G4P470	ZXXSB01
Sheet1

Here is a macro to replace the manual process (based on your present data structure) (tested with your data, and with multiple copes of your data):

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Press and hold down the 'ALT' key, and press the 'F11' key.

Insert a Module in your VBAProject, Microsoft Excel Objects

Copy the below code, and paste it into the Module1.

Option Explicit
Sub FillInSAPBlanks()

    Dim lngLastRow As Long
    lngLastRow = Range("F" & Rows.Count).End(xlUp).Row

    Application.ScreenUpdating = False

    Range("A2:F" & lngLastRow).Select
    Range("F" & lngLastRow).Activate
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = "=R[-1]C"

    With Range("A2:F" & lngLastRow)
        .Copy
        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
    End With
    Range("G1").Select

    Application.ScreenUpdating = True

End Sub

Then run the "FillInSAPBlanks" macro.

Have a great day,
Stan