BTW: thankyouthankyouthankyouthankyouthankyouthankyou :thanx:
Posts by missjoy
-
-
Be careful what you say... I need further help! The line that duplicates: I need to move the activity code 2 (column Q) into activity code 1 (column P) when the new line is duplicated. Does that make sense?
-
Once again - you have saved me shed loads of time - thanks so much... but can I ask one last thing? I ran the code, but it doesn't seem to copy the full row (it skips columns O & P)... I tried adjusting the code but can't seem to get it right - can you add a note where this happens? :thanx:
-
Thanks Carim - the only issue is that the 'blank' rows aren't actually blank - I create these by using the subtotal function (within the Data ribbon) so the 'blank' rows all have "*Total" in them.
I'm not sure how to edit what you've written since it's an IsEmpty.Just noticed there was an error in my After tab - please see the new attachment for the correct look :spin:
-
Hi Carim - good idea! Sorry I hadn't done that first :? I've now updated the file and attached to the initial post.
-
I have hundreds of thousands of rows of invoices for which I 'subtotal' if more than one code is assigned (change in Activity Code 2). I've uploaded a scaled down sample as it's difficult to explain. In the subtotal rows (4, 6, 9 & 14 on the attached) I want to duplicate the row above and add a "-2" to the PosnJobCode (column C), but ONLY if there is a subtotal row.
Using 'Go To' banks, =above, Ctrl+Entr duplicates data in column R that I don't want duplicated so I'm hoping a macro could help. However, I'm useless when it comes to loops, which I believe is needed to look through the sheet for the row containing 'Total', so am hoping someone here can help? Thanks in advance to anyone who wants to take this on!
-
Re: VBA Loop through, concatenate, add results to new sheet
:sing: That's perfect! Thanks so much it is EXACTLY what I was after - and your code's so clear I can really understand what going on - thanks again for your work on this!!
-
Re: VBA Loop through, concatenate, add results to new sheet
Thanks Carim, but I think I've not explained myself properly so I've attached a better example
I need each name concatenated with each code so that I have a list of all possible combinations... hopefully the spreadsheet explains better than me XD
-
Re: VBA Loop through, concatenate, add results to new sheet
Thanks Carim! :thanx:
-
Re: VBA Loop through, concatenate, add results to new sheet
Just noticed that the formula for adding the course code is a bit too simple: my codes are alpha numeric and not simply ascending/descending in value so that bit won't work (examples: CMSE11083, BUST11079, ACCN10014) and I want both the name and code in one cell.
-
Re: VBA Loop through, concatenate, add results to new sheet
Hi Carim - thanks so much for that - it will certainly fix my dilemma in the short term :thumbcoo:
The only reason I want a VBA is the fact that I'm dealing with 100's of rows for the names and over 1,000 rows of course names - both subject to changing when updates are made. So if you have any thoughts on the coding side - they'd be much appreciated!
-
Hi all, I need to concatenate two large lists in a somewhat complicated way as I need to use the results in a lookup. This is how my lists are presented :
In A1 - A5
Firstname Lastname1
Firstname Lastname2
Firstname Lastname3
Firstname Lastname4
Firstname Lastname5
In B1 - B6
CourseCode1
CourseCode2
CourseCode3
CourseCode4
CourseCode5
CourseCode6I need to loop to concatenate the two lists, adding the results to a new sheet, with results that look like this:
In A1 of a new sheet -
Firstname Lastname1 CourseCode1
Firstname Lastname1 CourseCode2
Firstname Lastname1 CourseCode3
Firstname Lastname1 CourseCode4
Firstname Lastname1 CourseCode5
Firstname Lastname1 CourseCode6
Firstname Lastname2 CourseCode1
Firstname Lastname2 CourseCode2
Firstname Lastname2 CourseCode3
Firstname Lastname2 CourseCode4
Firstname Lastname2 CourseCode5
Firstname Lastname2 CourseCode6... and so on until each of the names in column A (until blank) have been combined with each the codes from column B (until blank).I'm not good with writing loops and as such, this one really escapes me though will be a life saver if it can be done! Any help is MASSIVELY appreciated! Thanks in advance!
-
Re: Complex IF (AND (OR formula
Hi KjBox - apologies for missing that as IT WORKED :thanx: cheers for your help and reposting one of the solutions, it is appreciated!
-
Re: Complex IF (AND (OR formula
Hi all, further to the above I'm having issues again:
If one cell is blank I need the result to be "No value" when the 2 cells are added (D2+D3). It worked in my test sheet [where results were errors from blanks, I just added IFERROR = "No value"] but NOT with the live data [where it shows 3(3) if one cell is 3 and the other blank].
The other 'issue' (though not stopping my data from being used) is the fact that some of the results [5(1)] are coming up with negatives [5(-1)] -for obvious reasons... is there any way to stop this? I've adjusted my COUNTIF [COUNTIS(E3:P146,"=5(1)")+COUNTIFS(E3:P146,"=5(-1)")] to account for both (1) and (-1) but wondering if there's a slicker way to get this done?
Thanks again!
-
Re: Complex IF (AND (OR formula
Hi nikolassor - that worked a treat! Thanks for your help :smile:
-
Hello - I'm trying to get the following formula to show either a single number (i.e. or, where there is a variance (above/below 2/3) show a number with the variance (i.e. 6(2) ):
IF(AND(D2>2,D3<3,OR(D2<3,D3>2)),(D2+D3)&"("&(D2-D3)&")",(D2+D3))
Sadly, the results for D2=4, D3=4 are 8(0) where the result should be 8
If I use the same formula but swap the true and false statements (IF(AND(D2>2,D3<3, OR(D2<3,D3>2)), (D2+D3), (D2+D3)&"("&(D2-D3)&")") the brackets disappear, but even if D2=3 and D3=2 - which should show 5(1) :duh:
I have no idea what I've done wrong so any help would be hugely appreciated!
[INDENT]
PS the range in each cell (i.e. D2) is 1-4 which results with one of the following: 8, 7, 6, 6(2), 5(1), 5(3), 4, 2, 1
[/INDENT] -
Re: If And Formula Issues With Blanks
These cells happen to have bank account numbers... I know what you mean: it's weird that it didn't work 'cause it all makes sense. I've been having nothing but issues with this workbook - even my macros are onthe fritz: you can open it now and they work, give it 3 mins, open again (no changes) and theres an issue... I feel like :smash: !!!
-
Re: If And Formula Issues With Blanks
Let me explain better: 14 & 15 are referencing 2 different dbs, I need to see if 14 is feeding 15 the right info. I then need to check that there isn't human error going on by checking ss info - 16 -that is manually input.
The cells are reffering to bank details (that's why I haven't attached the ss itself) from these 3 places. If they are different from each other I need to show them on this new tab. But 16 doesn't HAVE to be there, but if it is, it needs to be the same as 14 & 15.
Daddylonglegs: I did try the long formula you posted, but somehow it ends up showing nothing when it should show that 14 & 15 are different :confused:
Oops re: the last (AND) over-zealous I guess!
-
I have searched the threads high and low, but no one seems to have come across this issue:
My formula works fine untill it comes across a blank in Comparison!E16:
=IF(AND(Comparison!E14=Comparison!E15,Comparison!E15=Comparison!E16),"",IF(AND(Comparison!E14=Comparison!E15,Comparison!E16=""),"") IF(AND(Comparison!E14<>Comparison!E15),Comparison!E14))
I am comparing cells from a previous sheet onto Comparison so that I can see where the discrepancies lie.
I want to:
return nothing if all the cells match;
return nothing if the first two match, but the third is blank;
return the value if the first cell isn't matchingkeeping in mind that this is only for the top cell: Comparison!14 - I will then have to bring it down to 15 and 16
Any thoughts on why this isn't working? THANKS :thanx:
-
Re: Lookup Text And Return Entire Row
Thanks for that - now its working?!?!? I checked if 'calculation' was on auto.... nope! Could be the reason that it didn't work before (DOH!) You're right - a nice little script would be good, but I'm trying to get this out by the end of the day, so something quick is good!
Thanks againg! : )