Posts by svenn

... after coming back to this, I worked again on the idea that the trick is that the ampersands are used two different ways: to concatenate the lookup value, and to string the formula together.

This works

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1" & "& ""_"" &" & "R3C[1], Sheet2!R3C3:R8C4," & "2, FALSE)"

Regards and thanks.

Thank you Carim. This is a small part of a large data manipulation and analysis solution within an Excel VBA workbook. I have a larger version of these two tables that are updated dynamically, and the VLookup formula is used to place key information from one data set beside another data set.

VBA formulas I have tried include:

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1" & ""_"" & "R3C[1], Sheet2!R3C3:R8C4," & "2, FALSE)"

I have a working VLookup formula placed into cell B3 in Table 1, copied below. The formula uses a concatenation of: $A3 & "_" & C$2 as the lookup value. Table 2 on Sheet2 has the same concatenation in column C, as shown on the attached 'Book1'.

=VLOOKUP($A3 & "_" & C$2, Sheet2!$C$3:$D$8, 2, FALSE)

I have tried to adapt this formula for use within VBA code, but have not hit on the right version.

Assistance gratefully received.

Thank you very much Carim. This is great and greatly appreciated!!

Thank you very much Carim. I looked at Power Query, and decided to stay with VBA for this.

I appreciate your assistance.

I am working to import and parse multiple text files into a single worksheet, using VBA. I have not found code that can readily be adapted, and I very much appreciate any guidance you can share.

Simplified version:

• Three text files (#101.txt #102.txt, and #103.txt) are to be imported into Sheet1.
• Each file contains three sets of data. Each data set will always have at least three rows, and at most four rows. It is the data in the fourth row of each data set (or absence of this data) that is key. The overall objective is to organize and parse data from the fourth row of each data set (if it exists), so that they can be compared. There is no way to predict when there will or will not be a fourth row – some data sets have three rows and unpredictably, some have four.
• In the example below, for file #101, the second data set (for “ITEM-b”) has only three rows; while the first and third data sets (for “ITEM-a” and “ITEM-c”) contain four rows of data.

File #101

(#101)-ITEM-a

-abc ITEM-a defg hij

(#101) [ignore this row]

text_#101a

(#101)-ITEM-b

-abc ITEM-b defg hij

(#101) [ignore this row]

(#101)-ITEM-c

-abc ITEM-c defg hij

(#101) [ignore this row]

text_#101c

• Highlighted below is the first of three data sets in file #101.txt. The VBA code should:
  • Lock in on the “Item-*” in the first row of each data set,
  • Look for the corresponding row based on the list of items in column A of Sheet1 (see attached file "FileWrangler.xlsx"),
  • And then copy the fourth row of the data set (if it exists), and paste it into the corresponding cell for that file (column) and that Item (row) in Sheet1.

(#101)-ITEM-a

-abc ITEM-a defg hij

(#101) [ignore this row]

text_#101a

• If a data set does not have a fourth row, then the corresponding cell in Sheet1 is left blank.
• As mentioned, the first row of each data set is used to identify the "Item", and the fourth row (if it exists) is what is copied into Sheet1. Rows 2 and 3 always exist, and can always be essentially ignored.
• Below is the desired final state of Sheet1, based on the contents of all three text files. In this example, only file #102 has all four rows for each of the three data sets.

Sheet1 Final State

I appreciate any guidance you can share. Attached are the three text files, and document "FileWrangler.xlsx", showing the desired results on Sheet1.

The part that threw me a curve was that I need to use:

=COUNTIFS(D:D,A1 & "-" & "*")

Doing it this way you are able to differentiate, for example, between "A-8" and "AB-3". But, my example oversimplified so this was not apparent.

Thanks again Fluff*!

Thank you Fluff*! Yes that works great. I appreciate it.

Thank you in advance for any enlightenment you can please offer!

Simple version:

1) Cells D1:D5 contain: A-8, ZZ-5, BBB-3, A-2, ZZ-7

2) Cells A1:A3 contain: A, BBB, ZZ

Note: Column A simply contains the prefixes of the items listed in column D

3) I need a formula in cells B1:B3 that will count the number of items in column D that have the prefix listed in column A.

4) With this example, the formula would generate the following values in cells B1:B3: 2, 1, 2

Thanks again, Sven

Alan, dangelor,

Thank you both! Just as I was replying to your message Alan, I saw the post from dangelor. The attached works great. Thank you! I will adapt it to add a row containing a table heading above each table.

Thanks again!

Sven

Simplified version: I have one main table with two columns and 100 rows. I want to filter Column 1 against a list of 10 different criteria (one of the following: A, B, C, D, E, F, G, H, I and J), one at time, and place the results in a second worksheet. This is the essence of what I want to do.

Suppose a main table on Sheet1 has two columns, with Column 1 randomly containing one of the following: A, B, C, D, E, F, G, H, I or J. I want to:

- Filter Column 1 on criteria 'A', copy the resulting table, and place it into Sheet2.

- Then, filter Column 1 on 'B', copy the resulting table, and place that below the first table on Sheet2.

- Similarly, loop through the remaining criteria: C, D, E, F, G, H, I or J, and place the resulting table below the other tables on Sheet2.

I would really appreciate any pointers to, for example, existing solutions that I could adapt to accomplish this.

Thank you, Sven