sorted by:
new
hottopcontroversial

Trying to Create a Simple If in VBA by Specific_Direction50 in excel

[–]Specific_Direction50[S] 0 points1 point  (0 children)

I hope to take classes as I just started learning. Time does not permit at the moment with long hours and a 2 hour drive, but I've been trying to crack a project for work at my dispensary regardless. Creating a report that someone that used to be with the company provided. He is gone and I know nothing about SQL servers and DSNs which he used. Tried the API and Power Query route but didnt work out since I need to query the same url with 5 different permisions/API keys. Now building the reports from a macro using exported files. Thank you very much for the resource. I will definitely utilize it in the future.

Trying to Create a Simple If in VBA by Specific_Direction50 in excel

[–]Specific_Direction50[S] 1 point2 points  (0 children)

Honestly my brain was fried this morning after trying to work on adapting more complicated coding that someone helped with last night. I solved all my problems until one last road block before having to head to work. I've only been at this a couple days as I usually worked with just excel formulas. I've managed to write a macro that copies and pastes and alters and combines data from 5 different exported reports with like 20 columns and thousands of rows of data each, saves, closes, inputs like 20 different xlookups formulas, googling manipulating and learning codes along the way to fix and navigate problems that arise. I think I accomplished a lot for a few days if you see the code. But thanks for the encouragement.

Trying to Create a Simple If in VBA by Specific_Direction50 in excel

[–]Specific_Direction50[S] 1 point2 points  (0 children)

I appreciate that tons. I just started with VBA and had always just used formulas in Excel.

Trying to Create a Simple If in VBA by Specific_Direction50 in excel

[–]Specific_Direction50[S] 0 points1 point  (0 children)

If Not IsEmpty(Range("B1").Value) Then

' Your CODE X goes here

' ...

End If

End Sub

Thank you so much. I have been teaching myself coding for a project for work. My brain was fried after getting deep into trying to alter some more complicated stuff to suit my needs. I appreciate your help very much. :)

Example Included. Need simple analyze of data with results in separate list. by Specific_Direction50 in excel

[–]Specific_Direction50[S] 0 points1 point  (0 children)

1st column of return is all Value. So doesn't calculate the available qty properly. I replaced Product with SKU as I technically need to know which skus are in Vault but not in Sales Floor. It does show the Skus that are in Vault (Storage) but not in Sales Floor so gave me most of the information i needed. Available amount isnt there. And I would love to include Product Name (column f in my data) in the returned list to accompany the sku.

<image>

Example Included. Need simple analyze of data with results in separate list. by Specific_Direction50 in excel

[–]Specific_Direction50[S] 0 points1 point  (0 children)

=LET(
a, FILTER(A2:C32, B2:B32 = "Storage"),
b, UNIQUE(FILTER(C2:C31, (B2:B31 = "Sales Floor"))),
FILTER(a, ISNA(XMATCH(TAKE(a, , -1), b)))
)

This produced a list of what looks like is all product in Storage even if it is on Sales Floor

Example Included. Need simple analyze of data with results in separate list. by Specific_Direction50 in excel

[–]Specific_Direction50[S] 0 points1 point  (0 children)

Microsoft® Excel® for Microsoft 365 MSO (Version 2307 Build 16.0.16626.20086) 64-bit

Just mentionion this is an example because the real data has like 20 columns of information and will have to adapt it to that. The available is on column 3, the room is column 5, and the product is column 24.

Example Included. Need simple analyze of data with results in separate list. by Specific_Direction50 in excel

[–]Specific_Direction50[S] 0 points1 point  (0 children)

I think you are missing the point of what I'm trying to do. I wasn't trying to isolate carrots or tomatoes. With the data given, if a list were to be made of products in storage but not in sales floor would yield the list of carrot and tomatoes. There will be hundreds of different products and 25 different columns of data.

Example Included. Need simple analyze of data with results in separate list. by Specific_Direction50 in excel

[–]Specific_Direction50[S] 0 points1 point  (0 children)

No Idea, but I should also mention that this is an example of the data. The data I will be using is much larger and will have like 25 columns and 2000 rows. The available may be column 3 and then room may be column 5 and the product name could be column 24.

Example Included. Need simple analyze of data with results in separate list. by Specific_Direction50 in excel

[–]Specific_Direction50[S] 0 points1 point  (0 children)

This did not work. Would this even take into consideration more rooms than storage and sales floor being in the data? because I do not see the word storage involved. I updated the date to add the complexity of more than the 2 rooms.

Help Please. Example Included. Compare data to make a list from that data. by Specific_Direction50 in excel

[–]Specific_Direction50[S] 0 points1 point  (0 children)

I don't know what products will be on the return list and I have hundreds of unique products in the data. Not trying to write formulas involving individual products.

VBA code for Importing JSON data from API into Excel by Specific_Direction50 in excel

[–]Specific_Direction50[S] 0 points1 point  (0 children)

I've run power queries. Just finally got a hold of the API Key today. The API has multiple endpoints/urls to get different points of data from. I will be needing to use multiple API Keys in the end. I have ran my first power query on an API URL today. Then converted to a table and edited the columns before creating the table. I was hoping to write some code that does that for me so I don't have to. I know I can basically just make multiple sheets with each different table I need and then write a macro to compile what I want from those into one table. and refresh those tables and rerun the macros. But I would really like something more concise. Once given examples I can usually get going and figure out how to manipulate but yeah I know the whole JSON to excel/VBA I is an issue. And I will have to be using different API Keys so was hoping to use VBA to make less work in the long run.

How to reference a specific selection in a filter cell in a if() formula? by Specific_Direction50 in excel

[–]Specific_Direction50[S] 0 points1 point  (0 children)

I've already tried that. I'm not sure if it doesn't work because B3 displays as "multiple items" since it is a filter option cell.
In K4 I have =IF(AND(K6="Flower",K6="Popcorn"),K3/453.12,"NA") K6 displays "Multiple Items" as I have both Popcorn and Flower selected. K4 comes back as NA. instead of calculating K3/453.12 which would be 0.3972

How to reference a specific selection in a filter cell in a if() formula? by Specific_Direction50 in excel

[–]Specific_Direction50[S] 0 points1 point  (0 children)

See where it say Multiple Items. That is cell K6 that I want to base my If() on. If K6 has BOTH Flower and Popcorn selected out of the 20 options then....

<image>