# Search:

Type: Posts; User: alansidman; Keyword(s):

Page 1 of 6 1

1. ## Thread: Stripping out data

by alansidman
Replies
6
Views
60

### Re: Stripping out data

A couple of steps in Power Query.

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1",...
2. ## Thread: Transpose Data to Create new table

by alansidman
Replies
10
Views
199

### Re: Transpose Data to Create new table

Zi,
A really good starting point for PQ is Ken Puls book "M is for (Data) Monkey"
3. ## Thread: Clubbing data values

by alansidman
Replies
3
Views
84

### Re: Clubbing data values

No further ideas without seeing your data. Suggest you upload a representative sample of your data to a third party site, ie. Dropbox.com, Box.net, etc.
4. ## Thread: SUM formula with dynamic divisor

by alansidman
Replies
10
Views
180

### Re: SUM formula with dynamic divisor

Why not just use the Average Function in Excel.
5. ## Thread: Clubbing data values

by alansidman
Replies
3
Views
84

### Re: Clubbing data values

Create a Pivot Table. Put A and B in the Rows. C in the Values
6. ## Thread: Macro Merge Particular sheet from multiple workbook

by alansidman
Replies
1
Views
40

### Re: Macro Merge Particular sheet from multiple workbook

Look at Power Query which allow you to merge tables or append tables. No VBA required. No formulas required. All done in the U/I.

by alansidman
Replies
1
Views
53

### Re: Merging sheet data to one spreadsheet?

You can use Power Query if you have Excel 2010 or later version to append each sheet. No VBA required.

by alansidman
Replies
4
Views
108

### Re: Move Numbers in adjacent cells

Will the Debits always be in the row directly below the credit?

by alansidman
Replies
1
Views
273

### Re: Link a CSV file in an Access database

With Access Open, click on External Data. Select Text file. Follow the wizard to create a link.
10. ## Thread: Macro to Sum Cells in Two Columns

by alansidman
Replies
7
Views
166

### Re: Macro to Sum Cells in Two Columns

Show us an example of your data and the expected solution. Be more specific in your explanation using your sample data for reference.
11. ## Thread: Excel Stocks not outputting some data

by alansidman
Replies
1
Views
77

### Re: Excel Stocks not outputting some data

Posting a picture does not really help as we cannot manipulate the data nor determine the formatting. You may wish to attempt to widen the column for the Market Cap as that is usually the issue with...
12. ## Thread: count cells that string contains X but does not contains Y or Z

by alansidman
Replies
7
Views
205

### Re: count cells that string contains X but does not contains Y or Z

Best I can do is to completely avoid Away and State with this code.

Option Explicit

Sub Directions()
Dim s1 As Worksheet, s2 As Worksheet
Dim lr1 As Long, lr2 As Long
Dim i...
13. ## Thread: count cells that string contains X but does not contains Y or Z

by alansidman
Replies
7
Views
205

### Re: count cells that string contains X but does not contains Y or Z

Your explanation is still not clear. Suggest you show us your expected results so that we can better understand.
14. ## Thread: return the row number when a match is found

by alansidman
Replies
2
Views
83

### Re: return the row number when a match is found

Your explanation in relation to your visual example makes no sense. Suggest you retry. You talk of searching AC:AE. Your example finds Dog in Column F. Why a two if the row is 7. Very confusing...
15. ## Thread: VBA code to copy current rows of filtered data

by alansidman
Replies
1
Views
89

### Re: VBA code to copy current rows of filtered data

VBA is not generic. Show us what your data looks like (sample). What should it be filtered on? Where will it be pasted once it has been copied? Without detailed specifics cannot provide you with...
16. ## Thread: count cells that string contains X but does not contains Y or Z

by alansidman
Replies
7
Views
205

### Re: count cells that string contains X but does not contains Y or Z

Which sheet are you referring to when you wish to exclude A4 and A5. What is the correct answer for each item? Confused by your request.
17. ## Thread: count cells that string contains X but does not contains Y or Z

by alansidman
Replies
7
Views
205

18. ## Thread: If statement help (if it has negative numbers)

by alansidman
Replies
9
Views
338

### Re: If statement help (if it has negative numbers)

How can a number be negative and be greater than -1. Are you referring to values between zero and -1 only, ie. -.5 or -.25 for example?

I am reading your request that you want "san" to appear if...
19. ## Thread: If statement help (if it has negative numbers)

by alansidman
Replies
9
Views
338

### Re: If statement help (if it has negative numbers)

=if(D43<>"","san", "")
20. ## Thread: Merging text files into one file...

by alansidman
Replies
4
Views
104

### Re: Merging text files into one file...

If you post a your workbooks to a third party site, I will give you my solution. The beauty of PQ is that once the data has been entered and the query built, any additional data added to the files...
21. ## Thread: Merging text files into one file...

by alansidman
Replies
4
Views
104

### Re: Merging text files into one file...

Look here for multiple tutorials on how to do this using Power Query
...
22. ## Thread: Remove time from a text cell

by alansidman
Replies
9
Views
246

### Re: Remove time from a text cell

Power Query will do that for you with the following Mcode

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Split Column by Delimiter" = Table.SplitColumn(Source,...
23. ## Thread: Sort List/Group, if matches value then return a value.

by alansidman
Replies
2
Views
99

### Re: Sort List/Group, if matches value then return a value.

Here is a VBA solution

Option Explicit

Sub SarahSteve()
Dim lrA As Long, lrC As Long, i As Long, j As Long
Application.ScreenUpdating = False
lrA = Range("A" &...
24. ## Thread: Allocation of Expenses

by alansidman
Replies
5
Views
272

### Re: Allocation of Expenses

1. You would be better served uploading an actual file and not a picture of a file. We cannot manipulate data in a picture and no one really wants to spend their time re-creating your file.
2. ...
25. ## Thread: Need Help! Formula for repetitive code

by alansidman
Replies
1
Views
102

### Re: Need Help! Formula for repetitive code

Show us some sample data. Are all sheets organized in the same manner. Also show us what the end result should look like. It helps to be able to visualize.
Results 1 to 25 of 150
Page 1 of 6 1