Excel Questions

[gerrykeegan]

I have quick question for the excel experts. I have some information I want sort quickly.
       A        B         C       D
1     alg     55        alg     55
2     als     45        als      45
3     avd    33        aox     47
4     aox    47        rst       35

Products are in A and  C amounts in  B & D
I need it to kick out a gap where the product isn't in both A&C
      A        B         C       D
1     alg     55        alg     55
2     als     45        als      45
3     avd    33       
4     aox    47        aox     47
5                           rst       35

Anyone?

Where do you need the gap, in row 3 you have it in column C, in row 5 you have it in column A

I need the gap where ever the product is not in both A&C. My first table is how my data looks now. My second table is the result of a sort I need to do. Product avd is not in C&D thats why I need a gap to appear. Product aox was not lined up in the first table is is in the second.

[Billys Boots]

You might be able to do something on a new sheet using the LOOKUP function, if the products were in alphabetical order on the first column/page.

[Hardy]

Gerry - see your "rst" in row 4.

Does this just appear once, in that position and do subsequent rows continue in normal order, with two sets of matching values?
As in:
Row 4: aox 47 rst 35
Row 5: xyz 75 xyz 75
Row 6: abc 90 abc 90

OR

Does the "rst" appear in the first column of row 5, with all the remaining values offset from there?
As in:
Row 4: aox 47 rst 35
Row 5: rst 35 xyz75
Row 6: xyz 75 abc 90

[ludermor]

I have quick question for the excel experts. I have some information I want sort quickly.
       A        B         C       D
1     alg     55        alg     55
2     als     45        als      45
3     avd    33        aox     47
4     aox    47        rst       35

Products are in A and  C amounts in  B & D
I need it to kick out a gap where the product isn't in both A&C
      A        B         C       D
1     alg     55        alg     55
2     als     45        als      45
3     avd    33       
4     aox    47        aox     47
5                           rst       35

Anyone?

Where do you need the gap, in row 3 you have it in column C, in row 5 you have it in column A

I need the gap where ever the product is not in both A&C. My first table is how my data looks now. My second table is the result of a sort I need to do. Product avd is not in C&D thats why I need a gap to appear. Product aox was not lined up in the first table is is in the second.

Will the total in B always equal the total in D?

[Hardy]

Try this:

https://www.dropbox.com/s/zfzpb8ogty5lf9k/Solution1.xlsx?dl=0

[gerrykeegan]

I have quick question for the excel experts. I have some information I want sort quickly.
       A        B         C       D
1     alg     55        alg     55
2     als     45        als      45
3     avd    33        aox     47
4     aox    47        rst       35

Products are in A and  C amounts in  B & D
I need it to kick out a gap where the product isn't in both A&C
      A        B         C       D
1     alg     55        alg     55
2     als     45        als      45
3     avd    33       
4     aox    47        aox     47
5                           rst       35

Anyone?

Where do you need the gap, in row 3 you have it in column C, in row 5 you have it in column A

I need the gap where ever the product is not in both A&C. My first table is how my data looks now. My second table is the result of a sort I need to do. Product avd is not in C&D thats why I need a gap to appear. Product aox was not lined up in the first table is is in the second.

Will the total in B always equal the total in D?

No in fact B & D will always be different, for simplicity I left them the same. I am combining two sets of data. A&C are the product code. I am trying to align each product code. B&D are data (say sales and quantities) of the product code. However not all products appear in A & C.

[gerrykeegan]

Product   Stock   Product   Sale
Apple   50           Apple   125
Melon   43          Banana   17
Orange   62          Melon   143
Peach   112          Pear      175
Pear             53      

1. Sales of banana but no stock         
2. Stock of orange but no sales         
3. Stock of peach but no sales         

When sort is completed         
Product   Stock   Product   Sale
Apple   50           Apple   125
                      Banana   17
Melon   43            Melon   143
Orange   62      
Peach   112      
Pear     53            Pear        175

Basically I want to line up the data without having to manually         
push down the data to line up.         

[laceer]

A pivot table might do it. Select all data, insert pivot table, drag Product in the row label, sum of sales and sum of stock into  values. Should give you a list of all products, the sum of their stock and sum if their sales. Where there are no stock or sales there will be a blank in your pivot table.

[macdanger2]

I presume you have a list of all of the possible items?

Say the raw data in the table below in in Excel in cells A1 to D6. Setup four new columns to the right (columns E to H) with the same headings (Product, Stock, Product, Sale).

In the two Product columns, paste in the full list of all possible items.

In column F, cell F2 input the following formula: "=VLOOKUP(E2,A:B,2,FALSE)"
In column H, cell H2 input the following formula: "=VLOOKUP(G2,C:D,2,FALSE)"

Drag that down to the end of your datasheet and it should do what you want.

Just to explain what you're doing with the VLOOKUP is:

1) Lookup the value in E2 (say "apple") in the matrix A:B (i.e. all rows in columns A-B)
2) if you find the value "apple" in column A then return the value in the second column of the matrix (i.e. column B).
3) The "FALSE" just means you're looking for an exact match. If no match is found, "N/A" is returned

[gerrykeegan]

That just gave me my raw data exactly as it was. Am I missing something. I got no gaps

[macdanger2]

Did you paste the FULL list of possible products in column E & G? That's where the gaps come from i.e. when something isn't found, n/a is returned

[gerrykeegan]

Did you paste the FULL list of possible products in column E & G? That's where the gaps come from i.e. when something isn't found, n/a is returned

I did I copy all the data into the adjacent cells. The formula worked but it didn't create the gaps. In the end I just manually inserted the gaps as I needed to get the job done. Thanks for your help and I now know something about Lookup!

[macdanger2]

No bother.

In the example below, you would have pasted apple, melon, orange, peach, pear, banana into both columns. Don't know why it wouldn't have worked

[majestic]

Hi

Hoping someone can help me with abit of vba, I need to find and replace alot of data, if i just use the standard across the entire sheet, its to big and excel just crashes. It's usually just done using the find and replace on a column by column basis! each column takes 15/20 mins before i can start the next. So i was thinking a macro could be written to just set this off once that can be left until they are all done. I was thinking of using the following:
Sub Replace"()
    Columns("A:BZ").Replace    What:=""", _
                            Replacement:="", _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByColumns, _
                            MatchCase:=False, _
                            SearchFormat:=False, _
                            ReplaceFormat:=False
End Sub

I was wondering if anyone knows whether this will run on a column by column basis or is it likely to crash just like the standard find and replace?

[macdanger2]

Sorry, don't know much about VBA.

I wouldn't have thought excel should crash on a find & replace though - how many rows of data are in the columns?