Function or macro to shift data if criteria is met

[matthewdbeard]

I am trying to move a large number of cells to the right one column and up one row if the cell contains certain text. Any help would be greatly appreciated

[pike]

Hi matthewdbeard
To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[matthewdbeard]

I will attach the spreadsheet with similar data for what I'm trying to do. When I pull data from this particular program, it comes out dirty, and this is as clean as I 've been able to get it. What I'm trying to do is lookup some numbers from the MASTER! spreadsheet against a sheet named PWWN_ALIAS. When it finds the pwwn I'm looking for, I want it to give me the ALIAS that corresponds to that #. The problem is that they are not on the same row and 2nd problem is that some of the names have multiple #'s. This may not even be possible but it would be the best thig since sliced bread if it could work.

[pike]

not really possible . how do you import the data?

[matthewdbeard]

The data in the sheet came out of a command prompt into a text file. I then cleaned it up as much as possible and put into excel. Would it work if I could somehow get all the pwwn #'s that correspond to the alias on the same line? Not really sure how to do that either. Possibly an IF function to concatenate the cells, skip the blank and then concatenate the next pwwn #'s.

[pike]

Hi matthewdbeard,
yes it is possible but i dont understand the data or see any patterns in the layout

[matthewdbeard]

There isn't a good pattern really. Only pattern is that all of the pwwn#'s listed under each Alias relate to that particular alias. I'm thinking that if I can get all of the pwwn#'s on one line for each Alias name, that the lookup will work much better.

[pike]

Hi matthewdbeard,
Can only get it with indirect but try

=LOOKUP(REPT("z",255),INDIRECT("PWWN_ALIAS!C1:C" &MATCH(MASTER!A1,PWWN_ALIAS!A:A,0)) )

[martindwilson]

cant you start by shifting col c down one row then filling in the blanks against the relevant pwnn with something like in d4
=IF(AND(A4="",C4=""),"",IF(AND(A4<>"",C4<>""),C4,IF(AND(A4<>"",C4=""""),D3,C3))) then you can look up any pwnn and get its alias

[pike]

Hi matthewdbeard
Is sheet ALBMDSCORE02_VSAN2 the same data unsorted ?

@ martin I have misread the question as there maybe more than one value of each lookup

[matthewdbeard]

Yes. ALBMDSCORE01_VSAN2 does list some of the pwwn #'s but not all of them. There are some sheets that I hid just because I thought seeing the 2 sheets would make it easier. And yes, some Alias names have multiple pwwn#'s. They are all different though. martindwilson, your function worked great, except on the Alias names that had 4,5, or 6 pwwn#'s.

[martindwilson]

show us some examples

[matthewdbeard]

I have uploaded the attachment with the changes. When you open it you will see an example of an Alias name with 4 and 5 pwwn#'s. Let me know if this helps.

Thanks,

Matthew

[martindwilson]

oops theres an error in the formula at the end
=IF(AND(A2="",C2=""),"",IF(AND(A2<>"",C2<>""),C2,IF(AND(A4<>"",C2=""""),D1,C1)))
should be
=IF(AND(A2="",C2=""),"",IF(AND(A2<>"",C2<>""),C2,IF(AND(A2<>"",C2=""),D1,C1)))
i corrected it in my zipped example but from only d11 down doh!

[matthewdbeard]

martin that worked great. Thanks again. You have no idea how big of a help this was. This site has been a really great place for me to find good information.

Matthew